convert a tall table to a flat table - sql help

[TWillard]

I am trying query a tall table with sql and have the oupt appear as a flat result set for easier reporting.

TALL table is what I have
-------------------------
1 a
2 b
3 a
4 a
5 c
6 d
7 e
8 f
9 g
10 a

FLAT result set is what I desire
---------------------------
1 2 3 4 5 6 7 8 9 10
a b a a c d e f g a

My initital thoughts include:
1) Rebuilding the table into a flat table. However, this will require a major re-work over some application code.
2) Creating a single sql statement and join the table to itself multiple times. I need to select 45 columns, so this would be a huge query. It would be a pain to write and may be even more painful to execute.

Any Ideas?

 

[NoCoolHandle]

I am assuming that you want your column1 to become the headding...

Something like (1 thought)

Select
case C1 when 1 then C2 end As [1],
case C1 when 2 then C2 end As [2],
case C1 when 3 then C2 end As [3],
case C1 when 4 then C2 end As [4],
case C1 when 5 then C2 end As [5],
case C1 when 6 then C2 end As [6],
.....
From TableX

 

[vongrunt]

Rotate display 90 degrees counter-clockwise. Any $50 nVidia/ATI can do that .

Seriously: this is called "transposition" and is NOT something to do with SQL. One of reason: if 1st column is integer and another one varchar, what should be result column? varchar? sql_variant?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[TWillard]

NoCoolHandle, Thanks for the reply. Your query does make the result set wide. It also maintains the tallness of table. Is there some way to roll-up the results.

Select
case C1 when 1 then C2 end As [1],
case C1 when 2 then C2 end As [2],
case C1 when 3 then C2 end As [3],
case C1 when 4 then C2 end As [4],
case C1 when 5 then C2 end As [5],
case C1 when 6 then C2 end As [6],
.....
From TableX

RESULTS
------------
1 2 3 4 5 6 7 8 9
1 a
2 b
3 a
4 a
5 c
6 d
7 e
8 f
9 a




FLAT result set is what I desire
---------------------------
1 2 3 4 5 6 7 8 9 10
a b a a c d e f g a

Thanks in Advance for Replys!

 

[vongrunt]

Wrap every case... end into MIN(), turn ansi_warnings off if necessary.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]