Transpose Table

Cross-tab, see http://www.aspfaq.com/2462

Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"Chris Akco" wrote in message
news:

How can i transpose a Table in Micrososft SQL... I have to switch rows
into columns. For example:

Col1 Col2 Col3
******************
aaaa 1111 2222
bbbb 3333 4444
cccc 5555 6666

And i Want to transpose it to:

Col1 Col2 Col3
******************
aaaa bbbb cccc
1111 3333 5555
2222 4444 6666

I have to do this in an SP.
Thank you very much
chris

To transpose a table in the manner you have shown is an unusual
requirement - in a normalized data model it wouldn't usually make sense. A
more common requirement is to produce a cross-tab report. See the link that
Aaron posted if that is what you need.

Because a table has no inherent row order, a row/column transformation would
require some attribute to determine which row should be transformed for the
1st column in the output, which row for the 2nd, etc. Since your example
doesn't give us any obvious numbered attributes to define which row you want
transformed to which column I assume you will want to specify that in your
query code. A query always has a fixed number of columns so you will have to
assume a fixed number of rows in your table unless you use dynamic SQL.

CREATE TABLE SomeTable (col1 CHAR(4), col2 CHAR(4), col3 CHAR(4), PRIMARY
KEY (col1,col2,col3))

INSERT INTO SomeTable VALUES ('aaaa', '1111', '2222')
INSERT INTO SomeTable VALUES ('bbbb', '3333', '4444')
INSERT INTO SomeTable VALUES ('cccc', '5555', '6666')

SELECT MIN(CASE c WHEN 'aaaa' THEN x END),
MIN(CASE c WHEN 'bbbb' THEN x END),
MIN(CASE c WHEN 'cccc' THEN x END)
FROM
(SELECT 1, col1, col1
FROM SomeTable
UNION ALL
SELECT 2, col1, col2
FROM SomeTable
UNION ALL
SELECT 3, col1, col3
FROM SomeTable) AS Z(r,c,x)
GROUP BY r

This assumes that all the columns are of compatible datatypes.

David Portas
SQL Server MVP