Convert each column of a record to a separate record
I have an entry:
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 to get the result of each column as separate records from two columns such as the first column will become the header of the original column and the second column should be the value of the source column, as the result below the query where I achieved the result 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
The problem is with this query - I have to define the columns explicitly, is there a way for it to dynamically get the column names and their values?
source to share
I like the method apply
for unwinding in SQL Server:
select v.*
from @tbl t cross apply
(values ('col1', col1),
('col2', col2),
('col3', col3)
) v(AttributeTitle, AttributeValue);
This simplifies the query, but does not directly answer the question.
If you need a variable number of columns, you will have to use dynamic SQL. It's a bit cumbersome on a real table. However, with a table variable or temporary table, you have an additional problem with the scoping rules - the table name will not be in scope when the statement is executed.
source to share