Pivoting/Unpivoting multiple columns using dynamic SQL

Question

I have the below requirement.

CREATE TABLE #TempData 
(
    Columnname1 NVARCHAR(50),
    Value1 NVARCHAR(50),
    Columnname2 NVARCHAR(50),
    Value2 NVARCHAR(50),
    Columnname3 NVARCHAR(50),
    Value3 NVARCHAR(50)
);

Insert sample data into the temporary table

INSERT INTO #TempData (Columnname1, Value1, Columnname2, Value2, Columnname3, Value3)
VALUES
('CustomerID', '1', 'CustomerName', 'A', 'CustomerCity', 'Hyd'),
('CustomerID', '2', 'CustomerName', 'B', 'CustomerCity', 'Mum'),
('CustomerID', '3', 'CustomerName', 'C', 'CustomerCity', 'Pune'),
('CustomerID', '4', 'CustomerName', 'D', 'CustomerCity', 'Nagpur'),
('CustomerID', '5', 'CustomerName', 'E', 'CustomerCity', 'Delhi'),
('CustomerID', '6', 'CustomerName', 'F', 'CustomerCity', 'Noida'),
('CustomerID', '1', 'CustomerDOB', 'July', NULL, NULL),
('CustomerID', '2', 'CustomerDOB', 'August', NULL, NULL),
('CustomerID', '3', 'CustomerDOB', 'September', NULL, NULL),
('CustomerID', '4', 'CustomerDOB', 'October', NULL, NULL),
('CustomerID', '5', 'CustomerDOB', 'November', NULL, NULL),
('CustomerID', '6', 'CustomerDOB', 'December', NULL, NULL);`

Above is just a sample. Apart from the columnname1, rest of the columns(Column name2,3 etc) can have multiple columns as shown in the columnname2.

So I need to make sure that the result can look as below.

Result set:

CustomerID  CustomerName    CustomerCity    CustomerDOB
--------------------------------------------------------
1           A               Hyd             July
2           B               Mum             August
3           C               Pune            September
4           D               Nagpur          October
5           E               Delhi           November
6           F               Noida           December

All this need to be dynamic as the column names are not static. It may vary with each and every requirement. Can you please provide me with the dynamic SQL in SQL Server 2014 only to make this work?

I tried the below. But I'm unable to make it work at once for an entire #temp table:

DECLARE @DynamicPivotQuery1 AS NVARCHAR(MAX)
DECLARE @DynamicPivotQuery2 AS NVARCHAR(MAX)
DECLARE @DynamicPivotQuery3 AS NVARCHAR(MAX)

DECLARE @ColumnName1 AS NVARCHAR(MAX)
DECLARE @ColumnName2 AS NVARCHAR(MAX)
DECLARE @ColumnName3 AS NVARCHAR(MAX)


-- Get distinct values of the PIVOT Column 
SELECT @ColumnName1 = ISNULL(@ColumnName1 + ',','') + QUOTENAME(Columnname1)
FROM (SELECT DISTINCT Columnname1 FROM #TempData) AS Columnnames

SELECT @ColumnName2 = ISNULL(@ColumnName2 + ',','') + QUOTENAME(Columnname2)
FROM (SELECT DISTINCT Columnname2 FROM #TempData) AS Columnnames

SELECT @ColumnName3 = ISNULL(@ColumnName3 + ',','') + QUOTENAME(Columnname3)
FROM (SELECT DISTINCT Columnname3 FROM #TempData) AS Columnnames

-- Create the dynamic PIVOT query
SET @DynamicPivotQuery1 = 
  N'SELECT DISTINCT ' + @ColumnName1 + ' 
    FROM 
    (
        SELECT Columnname1, Value1, Columnname2, Value2,Columnname3, Value3 
        FROM #TempData
    ) AS SourceTable
    PIVOT(MAX(Value1) 
          FOR Columnname1 IN (' + @ColumnName1 + ')) AS PivotedTable'

PRINT @DynamicPivotQuery1
EXEC sp_executesql @DynamicPivotQuery1

SET @DynamicPivotQuery2 = 
  N'SELECT ' + @ColumnName2 + '
    FROM 
    (
        SELECT Columnname1, Value1, Columnname2, Value2,Columnname3, Value3 
        FROM #TempData
    ) AS SourceTable
    PIVOT(MAX(Value2) 
          FOR Columnname2 IN (' + @ColumnName2 + ')) AS PivotedTable'

-- Execute the dynamic query
PRINT @DynamicPivotQuery2
EXEC sp_executesql @DynamicPivotQuery2

SET @DynamicPivotQuery3 = 
  N'SELECT ' + @ColumnName3 + '
    FROM 
    (
        SELECT Columnname1, Value1, Columnname2, Value2,Columnname3, Value3 
        FROM #TempData
        WHERE Columnname3 IS NOT NULL
    ) AS SourceTable
    PIVOT(MAX(Value3) 
          FOR Columnname3 IN (' + @ColumnName3 + ')) AS PivotedTable'
--MAX(CASE WHEN ColumnName1 = 'CustomerID' THEN Value1 END) as CustomerID
-- Execute the dynamic query
PRINT @DynamicPivotQuery3
EXEC sp_executesql @DynamicPivotQuery3

It can't really be fully dynamic, you must somehow know that CustomerID is an anchoring key, for example. — Aaron Bertrand
– Aaron Bertrand, Commented Oct 25, 2023 at 1:51
Yes, That's correct. CustomerID is the key column here. And in the rest of the columns might have multiple entries as shown in the columnname2. Can we make it dynamic by leaving the ID column? — subbu2464
– subbu2464, Commented Oct 25, 2023 at 1:58

Aaron Bertrand · Accepted Answer · 2023-10-25 02:28:09Z

1

When you have to PIVOT more than one column, I find it much easier to conceptualize using conditional aggregation. If we think backward from a conditional aggregate query that will generate the results you need, given that ColumnName1 is fixed and that Value1 will always hold the CustomerID:

SELECT CustomerID = Value1, 
  CustomerName = MAX(CASE WHEN ColumnName2 = 'CustomerName' THEN Value2 END),
  CustomerCity = MAX(CASE WHEN ColumnName3 = 'CustomerCity' THEN Value3 END),
  CustomerDOB  = MAX(CASE WHEN ColumnName2 = 'CustomerDOB'  THEN Value2 END)
FROM #TempData
GROUP BY Value1;

Since you are on an ancient and unsupported version without the benefit of STRING_AGG(), we can get there from dynamic SQL using FOR XML PATH:

DECLARE @cases nvarchar(max),
        @sql   nvarchar(max);

WITH x AS 
(
  -- one way to UNPIVOT first is to use UNION ALL
  SELECT s = N'ColumnName2', c = ColumnName2, v = N'Value2'
    FROM #TempData GROUP BY ColumnName2
  UNION ALL 
  SELECT s = N'ColumnName3', c = ColumnName3, v = N'Value3'
    FROM #TempData GROUP BY ColumnName3
)
SELECT @cases = (SELECT CONCAT(',', char(13), char(10), 
    QUOTENAME(c), N' = MAX(CASE WHEN ', s, 
    N' = ', QUOTENAME(c, char(39)), N' THEN ', v, N' END)') 
    FROM x WHERE c IS NOT NULL
    FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)');

SELECT @sql = CONCAT(N'SELECT CustomerID = Value1',
  @cases, N'
  FROM #TempData GROUP BY Value1;');

PRINT @sql;
EXEC sys.sp_executesql @sql;

Example db<>fiddle

answered Oct 25, 2023 at 2:28

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

subbu2464 Over a year ago

Thank you Aaron. This also works. I will try to use the best solution which will not kill the performance because I need to apply this in PROD on millions of rows. Appreciate your response.

Squirrel · Accepted Answer · 2023-10-25 02:09:43Z

-1

Assuming that Columnname1 is fixed. Use cross apply() to unpivot your data first, then it is easier to use a pivot query.

declare @cols nvarchar(max),
        @sql  nvarchar(max);

select @cols = isnull(@cols + ',', '') + quotename(Columnname)
from   #TempData
       cross apply
       (
           values
           (Columnname2), (Columnname3)
       ) c (Columnname)
group by Columnname


select @sql = N'select * '
            + N'from 
                (
                    select Value1 as CustomerID, Columnname, Columnvalue
                    from   #TempData
                           cross apply
                           (
                                values
                                (Columnname2, Value2),
                                (Columnname3, Value3)
                           ) c (Columnname, Columnvalue)
               ) d
               pivot
               (
                   max (Columnvalue)
                   for Columnname in (' + @cols + ')
               ) p'

print @sql

exec sp_executesql @sql


-- the dynamic query
select * 
from 
(
     select Value1 as CustomerID, Columnname, Columnvalue
     from   #TempData
            cross apply
            (
                values
                (Columnname2, Value2),
                (Columnname3, Value3)
            ) c (Columnname, Columnvalue)
 ) d
 pivot
 (
     max (Columnvalue)
     for Columnname in ([CustomerCity],[CustomerDOB],[CustomerName])
 ) p

answered Oct 25, 2023 at 2:09

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

1 Comment

subbu2464 Over a year ago

Thank you so much for your reply. This is giving me the result as I expected. But, let me apply the same in my original scenario and see how it perform. Thank you once again. Hopefully this should work. If anything is not working as expected, I will post the same here.

Collectives™ on Stack Overflow

Pivoting/Unpivoting multiple columns using dynamic SQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related