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

Answer 1

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;