Convert each column of a record into separate record

Question

I have a record:

DECLARE @Tbl AS TABLE(Col1 VARCHAR(10), Col2 VARCHAR(10), Col3 VARCHAR(10));

INSERT INTO @Tbl 
VALUES('Val1', 'Val2', 'Val3')

-- Source Record
SELECT Col1, Col2, Col3 FROM @Tbl

Result: Val1 Val2 Val3

I want result of each column as separate two column records like first column will become the title of source column and second column should be the value of source column like the result of below query in which I have achieved the result by UNION ALL :

--Query for Target Result
SELECT 'Col1' AttributeTitle, CONVERT(VARCHAR, Col1) AttributeValue FROM @Tbl
UNION ALL SELECT 'Col2' AttributeTitle, CONVERT(VARCHAR, Col2) AttributeValue FROM @Tbl
UNION ALL SELECT 'Col3' AttributeTitle, CONVERT(VARCHAR, Col3) AttributeValue FROM @Tbl

Problem in this query is I have to explicitly define the columns, is there any way that it should dynamically get the columns names and their values?

Answer 1

You could use UNPIVOT but you still need to know the names of the columns.

SELECT ColumnName, ValueName 
FROM (SELECT * FROM @Tbl) AS Data
UNPIVOT
(   ValueName
    FOR ColumnName IN (Col1, Col2, Col3)
) AS PivottedOutput