Cross-tab reports

8 Jul 2005 2:41 PM

C-W

If you look up "Cross-tab reports" in books online it gives you an example
where you can show Quarters as columns and Years as rows. Is it possible to
generate a query where the number of columns is unknown?

For example, I want to show passenger names accross the top. I have a table
structure with three tables... Passengers, Items and PassengerItems. I
would like to list all the items vertically, all the passengers horizontally
and I would like to show a 1 or 0 bit value as to whether a particular
passenger has purchased a particular item.

E.g.

                Smith   Jones    Evans
---------------------------------
Item 1        1            0            0
Item 2        1            1            1
Item 3        1            0            0

Is this possible?

Thanks

8 Jul 2005 3:21 PM

Aaron Bertrand [SQL Server MVP]

Not really in SQL, at the very least it would have to dynamically go figure
out how many there would be.

This is much better accomplished in a report writer, the presentation
front-end, Excel, etc.

A

Show quote

"C-W" <nomailplease@microsoft.nospam> wrote in message
news:%23dUUxs8gFHA.4000@TK2MSFTNGP12.phx.gbl...
> If you look up "Cross-tab reports" in books online it gives you an example
> where you can show Quarters as columns and Years as rows. Is it possible
> to generate a query where the number of columns is unknown?
>
> For example, I want to show passenger names accross the top. I have a
> table structure with three tables... Passengers, Items and PassengerItems.
> I would like to list all the items vertically, all the passengers
> horizontally and I would like to show a 1 or 0 bit value as to whether a
> particular passenger has purchased a particular item.
>
> E.g.
>
>                Smith   Jones    Evans
> ---------------------------------
> Item 1        1            0            0
> Item 2        1            1            1
> Item 3        1            0            0
>
> Is this possible?
>
> Thanks
>

8 Jul 2005 3:38 PM

C-W

Thanks, I was originally going to do this through the front-end, but I
thought I would check first.

Show quote

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:uT5jZC9gFHA.3436@tk2msftngp13.phx.gbl...
> Not really in SQL, at the very least it would have to dynamically go
> figure out how many there would be.
>
> This is much better accomplished in a report writer, the presentation
> front-end, Excel, etc.
>
> A
>