Dynamic Pivot - SQL

Question

L1  L2  L3  Year         ID
----------------------------------
0    0   1    2019        1
1    0   0    2020        2
------------------------------------

L1,L2,L3 these are Dynamic Columns Can go up to L5

i,e All possible combinations of L1 to L5

Can you please help me in Dynamic UnPivot in SQL Server

Expected Output:

         Year    ID
------------------------------
L1   0    2019    1
L2   0    2019    1
L3   1    2019    1
L1   1    2020    2
L2   0    2020    2
L3   0    2020    2

What is it you need help with? What have you tried? Why isn't it working? If you *know* that the columns will be `L1`, `L2`, `L3`, `L4`, and `L5` why do you *need* it to be dynamic? Dynamic Pivots are for when you *don't* know what the columns will be, but you appear to know what they will be. — Thom A, Mar 29 '23 at 10:51
If you really do need a dynamic pivot (which it doesn't sound like you do), check out [this question(https://stackoverflow.com/questions/46217564/converting-single-row-into-multiple-rows-based-on-values-in-columns) and [this answer](https://stackoverflow.com/a/46645342/5669294). — 3N1GM4, Mar 29 '23 at 11:28
Columns will be dynamic, It will be all possible permutation combinations Such as For 2 :L1 L2; L1 L3; L1 L4; L1 L5; L2 L3; L2 L4; L2 L5; L1 L2 L3; L1 L3 L4; L1 L4 L5; L2 L3 L4; L2 L4 L5; L3 L4 L5; — user2545157, Mar 29 '23 at 11:49

gotqn · Accepted Answer · 2023-03-29T12:29:17.450

There is no need to care fore columns which does not have values. If your current filtration does not include values for some of the columns, there are going to be skipped in the UNPIVOT result event you have specified them in the clause. For example:

DROP TABLE IF EXISTS #DataSource;

CREATE TABLE #DataSource
(
    L1 int,
    L2 int,
    L3 int,
    L4 int,
    L5 int,
    Year int,
    ID int
);

INSERT INTO #DataSource (L1, L2, L3, Year, ID)
VALUES (0, 0, 1, 2019, 1),
       (1, 0, 0, 2020, 2);

SELECT * 
FROM #DataSource;

SELECT * 
FROM #DataSource
UNPIVOT
(
    [value] FOR [column] IN ([L1], [L2], [L3], [L4], [L5])
) UNPVT;

 DROP TABLE IF EXISTS #DataSource;

CREATE TABLE #DataSource
(
    L1 int,
    L2 int,
    L3 int,
    L4 int,
    L5 int,
    Year int,
    ID int
);

INSERT INTO #DataSource (L1, L2, L3, Year, ID)
VALUES (0, 0, 1, 2019, 1),
       (1, 0, 0, 2020, 2);

SELECT * 
FROM #DataSource;


DECLARE @DynamicTSQLStatement NVARCHAR(MAX)
       ,@DynamicColumns NVARCHAR(MAX);

SELECT @DynamicColumns = STRING_AGG(CAST(QUOTENAME([name]) AS NVARCHAR(MAX)), ',')
FROM [tempdb].[sys].[columns] 
WHERE [object_id] = OBJECT_ID('tempdb..#DataSource')
    AND [name] NOT IN ('ID', 'Year')

SET @DynamicTSQLStatement = N'
SELECT * 
FROM #DataSource
UNPIVOT
(
    [value] FOR [column] IN (' + @DynamicColumns +')
) UNPVT;';
 
 EXEC sp_executesql @DynamicTSQLStatement;

Dynamic Pivot - SQL

1 Answers1