This Function Generates UNPIVOT Syntax

September 7, 2022

This Function Generates UNPIVOT Syntax

Filed under: Miscelleaneous SQL,SQL Scripts,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 12:00 pm

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.

-- Comments (7)

7 Comments »

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…

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 ISNULL(U.FieldName,x.name) as FieldName, U.FieldValue
FROM 
	('
 
		+
		STUFF(
				(
				SELECT STRING_AGG( ' UNION ALL SELECT ''' + CAST( name AS NVARCHAR(MAX) ) + ''' as name ', ' ' ) 
				FROM sys.dm_exec_describe_first_result_set(@sql, DEFAULT, DEFAULT)
				) ,
				1,
				10,
				'') +
	')as x(name)
	LEFT JOIN 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
  ON x.name = u.FieldName';
END
GO

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

CREATE OR ALTER   FUNCTION [dbo].[GenerateUnpivotSql2] (@Sql NVARCHAR(MAX))
  RETURNS NVARCHAR(MAX) AS
BEGIN 
RETURN 
	'
select k.[key] as fieldName,k.value as fieldValue
from 
(
	'
	+ @sql + '
	for json AUTO , WITHOUT_ARRAY_WRAPPER , INCLUDE_NULL_VALUES
)as a(a)
cross apply openjson(a.a) as k';
END
GO
 
 
declare @sql nvarchar(max) ='SELECT * FROM sys.databases WHERE database_id = 2';
declare @newsql nvarchar(max) = dbo.GenerateUnpivotSql2 (@sql);
exec sp_executesql @sql;
exec sp_executesql @newsql;
GO

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:

DECLARE @sql NVARCHAR(MAX) ='SELECT name, database_id, compatibility_level, collation_name, state_desc FROM sys.databases WHERE database_id = 2';
DECLARE @Clunky NVARCHAR(MAX) = 
CONCAT(N'
    SELECT
        X2.n.value (''local-name(.)'', ''nvarchar(128)'') AS FieldName,
        X2.n.value (''text()[1]'', ''nvarchar(max)'') AS FieldValue
    FROM (', 
    @sql, 
    N'FOR XML RAW, ELEMENTS XSINIL, TYPE
         ) AS X1 (X)
        CROSS APPLY X1.x.nodes(''//*'') AS X2 (n)
    WHERE LEN(X2.n.value (''local-name(..)'', ''nvarchar(128)'') ) &gt; 0
    /* ORDER BY [FieldName] */ ;', '');
EXEC sys.sp_executesql @Clunky;

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

RSS feed for comments on this post. TrackBack URL

Michael J. Swart

September 7, 2022