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
Wage 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)
