How to convert a column header and its value into row in sql?

sql, sql-server-2008

Solution

Maybe something like this:

Test data

DECLARE @T TABLE(Col1 INT, Col2 INT, Col3 INT)
INSERT INTO @T
VALUES (1,1,1)

Query

SELECT
    *
FROM
(
    SELECT
        t.Col1,
        t.Col2,
        t.Col3
    FROM
        @T AS t
) AS SourceTable
UNPIVOT
(
    Value FOR Col IN
    (Col1,Col2,Col3)
) AS unpvt

Output

1   Col1
1   Col2
1   Col3

Problem

I have a table with columns say `col1, col2, col3`. The table has many rows in it. Let's assume `val1, val2, val3` is one such row. I want to get the result as ``` Col1, Val1 Col2, Val2 Col3, Val3 ``` That is 3 rows - one for each column and its value. I am using SQL Server 2008. I read about pivots. Are pivots a way to solve this problem? Can someone route me to some examples or solutions how to solve this problem? Thanks a lot

Original source