how to convert rows to colums

[vprakash]

Hi,

Could someone tell me how to flip an SQL Server 7 table. I want to convert Rows to columns, but we don't know how many rows exist for a key. There could be as many as 10 rows.

For example, Rows in a table are
Key, A
KEY, B
KEY, C
KEY, D

Desired Output is
KEY, A, B, C, D

Thanks for your help.

Regards,
Vinai
email: vinai.prakash@compaq.com

 

[MalcolmW]

Do you want a new table, or just some SQL to ouput the existing table in a flattened form?

 

[vanoordt]

This is a very strange thing to want in a RDBMS, however you can dynamically build an SQL statement and execute it with EXEC. You can thus select all the rows and create a table with exactly that number of columns, to enter the data you can do something similar.

 

[shyamMelwani]

I will use an example and try to explain how to work around this problem. It works.
It is called building perspectives from a table. It is also called building cross tabs from a table.

Assume the following table

Investors_ Table

Investor_ID[tab]Year[tab] Amount

[tab]1[tab][tab][tab][tab]2000[tab]12.00
[tab]2[tab][tab][tab][tab]2000[tab]13.00
[tab]1[tab][tab][tab][tab]1999[tab]12.00
[tab]2[tab][tab][tab][tab]1999[tab]11.00


Your required output is probably the following if I have got your problem right:

Investor Year1999[tab] Year2000

[tab]1[tab][tab]12.00[tab][tab][tab]1200
[tab]2[tab][tab]11.00[tab][tab][tab]13.00

If the above is your required output.
Use the following query for the table represented as Investors_ Table.

SELECT
[tab]INVESTOR_ID,
[tab]SUM (CASE WHEN (Year = 1999) THEN Amount Else 0 END) AS Year1999,
[tab]SUM (CASE WHEN (Year = 2000) THEN Amount Else 0 END) AS Year2000
FROM
[tab]INVESTORS_TABLE
GROUP BY
[tab] INVESTOR_ID

This is assuming that the query is written in SQL server.

 

[Husein]

shyamMelwani this is all fine for two or three records. But when you want to create a pivot table with bigger number of rows, this would be very complicated procedure. In way it is pity that SQL Server does not have pivot table ability like the one in Access.