Transpose rows to columns - SQL2005

I have the following result set

Status       Wage     --Columns Names (Table)
======       =====   
Employed    10,000  

What I need is:

Key     Value      DataType
===     =====      ========
Status  Employed   column data type e.g varchar
W开发者_JAVA技巧age    10,000     decimal(18,2)

How can this be achieved using TSQL in SQL 2005

What you are looking for is PIVOT.

http://msdn.microsoft.com/en-us/library/ms177410(v=SQL.90).aspx

It can be done using INFORMATION_SCHEMA, UNPIVOT, CAST and EXEC. Here's a working solution.

CREATE TABLE #Data ([Status] int, [Wage] varchar(100), [Tax] decimal(10,3), BigText nvarchar(max))
INSERT #Data VALUES (7, '$12m', 123123.22, 'small island')

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE
+ CASE WHEN CHARACTER_MAXIMUM_LENGTH > 0 THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS nvarchar(10)) + ')' WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN '(max)' ELSE '' END
+ CASE WHEN DATA_TYPE IN ('numeric', 'decimal') AND NUMERIC_PRECISION > 0 THEN '(' + CAST(NUMERIC_PRECISION AS nvarchar(10)) 
    + CASE WHEN NUMERIC_SCALE > 0 THEN ',' + CAST(NUMERIC_SCALE AS NVARCHAR(10)) ELSE '' END
 + ')' ELSE '' END DATA_TYPE
INTO #Columns
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME LIKE '#Data%'
ORDER BY C.ORDINAL_POSITION

DECLARE @selectList nvarchar(max), @columnNames nvarchar(max)

SELECT @selectList = ISNULL(@selectList + ',', '') + 'CAST(' + QUOTENAME(COLUMN_NAME) + ' AS nvarchar(max)) ' + QUOTENAME(COLUMN_NAME),
    @columnNames = ISNULL(@columnNames + ',', '') + QUOTENAME(COLUMN_NAME)
FROM #Columns

DECLARE @unpivot nvarchar(max)

SET @unpivot = '
    SELECT b.Column_Name, b.DataValue, c.Data_Type
    FROM
    (
        SELECT ' + @selectList + ' FROM #Data
    ) a
    UNPIVOT (DataValue FOR Column_Name IN (' + @columnNames + ')) b
    JOIN #Columns c ON c.Column_Name = b.Column_Name
'

EXEC (@unpivot)