Michael J. Swart

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:
Results

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.

7 Comments »

  1. 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

  2. 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

  3. […] Michael J. Swart has a function: […]

    Pingback by Building UNPIVOT Syntax – Curated SQL — September 8, 2022 @ 8:05 am

  4. 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

  5. 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)'') ) > 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

  6. Thanks Micheal! Yet another of your efforts to place in my KGBTools collection.

    Comment by Kevin Boles — September 26, 2022 @ 1:56 pm

  7. 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

Leave a comment

Powered by WordPress