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

Soalga used Ask the Experts™
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.
Watch Question

Do more with

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


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

SELECT * FROM <table>
      PIVOT (
            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?
Then you have to write a dynamic SQL statement.


is an introduction.

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