A crosstab variation

[Danster]

Gday all,
I trying to do a SQL view which is sort of like a crosstab. I've seen the BOL example but can't get it to apply to my scenario.

I already have a view which outputs two columns thus:

Col1 Col2
1 B
1 C
1 F
2 D
3 B
3 A
4 E
4 B
4 C
4 D

What I want to show is one line for each Col1 value and another column for each possible value of Col2 & have a 'Y' when that line has the Col2 value. Like this...

Col1 A B C D E F
1 Y Y Y
2 Y
3 Y Y
4 Y Y Y Y

That is hard enough (for me anyway).
Further complexity when I have simplified the example above! In actual fact, the A, B, C, D etc are string values of whole words which I don't know what they'll be. I'm hoping that in the SELECT statement, I read the field value and make it the column headings, but I don't know how many unique values there'll be all up (I DO know it'll be a maximum of 30ish values).
Lets say I'm getting a list of customers and I dunno, say transportation preferences.
Using my sample above, my source data may read

J Smith Bike
J Smith Car
J Smith Bus
Mr Jones Tram
Mr Peters Moped
Mr Peters Bike
Mrs Smith Car
Mrs Smith Scooter

and I want to see

Person Bike Car Bus Tram Moped Scooter
J Smith Y Y Y
Mr Jones Y
Mr Peters Y Y
Mrs Smith Y Y

without even knowing what the different transportation types there are.

Hope this explains enough. I'm using SQL Server 2000.
cheers

Danster

 

[zhavic]

I thing it can't be done using view.
View can't use variable count of columns, as you need.
Can it be a stored procedure ?

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[donutman]

Try this Crosstab Query - PART II (Dynamic Execution) faq183-5278
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[oyam]

donutman,

I copied your incredible crosstab procedure, but for some reason, I get bunch of errors trying to create. I am sure I am doing something wrong. Thanks. Osher

Server: Msg 170, Level 15, State 1, Procedure spCrossTabRun, Line 50
Line 50: Incorrect syntax near ','.
Server: Msg 156, Level 15, State 1, Procedure spCrossTabRun, Line 118
Incorrect syntax near the keyword 'execute'.
Server: Msg 156, Level 15, State 1, Procedure spCrossTabRun, Line 120
Incorrect syntax near the keyword 'insert'.
Server: Msg 137, Level 15, State 1, Procedure spCrossTabRun, Line 147
Must declare the variable '@Cnt'.
Server: Msg 137, Level 15, State 1, Procedure spCrossTabRun, Line 147
Must declare the variable '@AllParameters'.
Server: Msg 137, Level 15, State 1, Procedure spCrossTabRun, Line 148
Must declare the variable '@Cnt'.
Server: Msg 137, Level 15, State 1, Procedure spCrossTabRun, Line 149
Must declare the variable '@AllParameters'.
Server: Msg 137, Level 15, State 1, Procedure spCrossTabRun, Line 150
Must declare the variable '@Cnt'.
Server: Msg 137, Level 15, State 1, Procedure spCrossTabRun, Line 150
Must declare the variable '@ParseParam'.
Server: Msg 137, Level 15, State 1, Procedure spCrossTabRun, Line 151
Must declare the variable '@End'.
Server: Msg 156, Level 15, State 1, Procedure spCrossTabRun, Line 157
Incorrect syntax near the keyword 'ELSE'.
Server: Msg 170, Level 15, State 1, Procedure spCrossTabRun, Line 213
Line 213: Incorrect syntax near '='.
Server: Msg 156, Level 15, State 1, Procedure spCrossTabRun, Line 216
Incorrect syntax near the keyword 'ORDER'.