Swap Rows / Cols in a SQL Server 2005 Query / View

Soalga
Soalga used Ask the Experts™
on
I have a table under SQL Server 2005 with two columns (col1, col2) and this table have about 10 records, so the table look like this:

COL1        COL2
=============
val1          val_1
val2          val_2
...
valN          val_N

what's the best way to swap rows for cols to obtain some like this in a view:

val1     val2      ...   valN
====================
val_1   val_2   ...   val_N,

where the values of COL1 becomes the column names of the result, and the values of COL2 the values for each new column.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
well... thx... i do that... like this

SELECT * FROM <table>
      PIVOT (
            max(COL2)
            FOR COL1 IN([COL1], [COL2], ..., [COLN])
      ) AS PVT

But, what about if i have an unknown number of columns, i mean, i have "about" 10 records (that becomes the columns), how can i determine the IN(<cols>) sintax?
Commented:
Then you have to write a dynamic SQL statement.

http://www.sqlteam.com/item.asp?ItemID=4599

is an introduction.
Commented:

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial