Just like PIVOT syntax, UNPIVOT syntax is hard to remember.
When I can, I prefer to pivot and unpivot in the application, but here’s a function I use sometimes when I want don’t want to scroll horizontally in SSMS.
CREATE OR ALTER FUNCTION dbo.GenerateUnpivotSql (@Sql NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) AS BEGIN RETURN ' WITH Q AS ( SELECT TOP (1) ' + ( SELECT STRING_AGG( CAST( 'CAST(' + QUOTENAME(NAME) + ' AS sql_variant) AS ' + QUOTENAME(NAME) AS NVARCHAR(MAX) ), ', ' ) FROM sys.dm_exec_describe_first_result_set(@sql, DEFAULT, DEFAULT) ) + ' FROM ( ' + @sql + ' ) AS O ) SELECT U.FieldName, U.FieldValue FROM Q UNPIVOT (FieldValue FOR FieldName IN (' + ( SELECT STRING_AGG( CAST( QUOTENAME(name) AS NVARCHAR(MAX) ), ', ' ) FROM sys.dm_exec_describe_first_result_set(@sql, DEFAULT, DEFAULT) ) + ' )) AS U'; END GO |
And you might use it like this:
declare @sql nvarchar(max) ='SELECT * FROM sys.databases WHERE database_id = 2'; declare @newsql nvarchar(max) = dbo.GenerateUnpivotSql (@sql); exec sp_executesql @sql; exec sp_executesql @newsql; |
to get results like this:
Uses
I find this function useful whenever I want to take a quick look at one row without all that horizontal scrolling. Like when looking at sys.dm_exec_query_stats
and other wide dmvs. This function is minimally tested, so caveat emptor.
I was just looking
at doing this the
other day, but got
side-tracked…
Nice to have a
solution in my
“back pocket” –
Thanks, Michael!
Comment by Scott Stauffer — September 7, 2022 @ 3:23 pm
In case we need to display the field with NULL value…
Comment by Sababa — September 8, 2022 @ 2:37 am
[…] Michael J. Swart has a function: […]
Pingback by Building UNPIVOT Syntax – Curated SQL — September 8, 2022 @ 8:05 am
There is also another way to unpivot, with the help of the json
Comment by Sababa — September 9, 2022 @ 7:35 am
Great solution, would have helped me a few years ago. 🙂
Then I found a solution on the internet, like this:
The XML-solution will fail on certain datatypes, like in this case owner_sid, a varbinary(85). So the function GenerateUnpivotSql is a better solution, will place it my toolkit. Thank you!
Love the use of sys.dm_exec_describe_first_result_set.
Comment by gserdijn — September 14, 2022 @ 2:05 am
Thanks Micheal! Yet another of your efforts to place in my KGBTools collection.
Comment by Kevin Boles — September 26, 2022 @ 1:56 pm
Works on SQL 2017 and up. Not on my 2016 version 🙁
Comment by Scott S — September 28, 2022 @ 2:58 pm