from rows into columns

[mikmak]

hi,
does anybody know how to change a table with some values: row_A(1), row_A(2), row_A(3) into a table with values: column_A(1), column_B(2), column_C(3) with a stored procedure?

Ex.
column_A column_A | column_B | column_c
row_A 1 1 2 3
row_A 2 =>
row_A 3

thanks
John

 

[tlbroadbent]

If your data is structured as in your example, a query like the following will produce the "cross-tab" or "pivot table" result that you want.

Select Col1,
Max(Case When Col2=1 Then Col2 else null end) as Col_A,
Max(Case When Col2=2 Then Col2 else null end) as Col_B,
Max(Case When Col2=3 Then Col2 else null end) as Col_C
From TableName
Group By Col1

However, if the data is more complex, you'll need to modify the CASE statements. You should also examine a Pivot generation script posted at SWYNK.COM.

http://www.swynk.com/sqlscripts/misc7.asp

The actual script can be downloaded from...

http://www.swynk.com/downloads/sp_Query_Pivot.sql

Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm