How to create dynamic columns in a pivot

Question

So I have two tables and I want to pivot a table and the number of columns in the pivot table should be based on how many columns I want.

This is the first table

DefectiveTableList

SysID  |    DefectClassificationName  | CreatedAt
-------|------------------------------|-------------
    4  |    Wrong Color               | 2022-08-26 16:31:20.470
    5  |    Paper Is Thin             | 2022-08-26 16:31:20.470
    6  |    Misaligned Print          | 2022-08-26 16:31:20.470
    7  |    Defective Device          | 2022-08-26 16:31:20.470
    8  |    Delamination              | 2022-08-26 16:31:20.470
    9  |    Burned Lamination         | 2022-08-26 16:31:20.470
   10  |    Cracked Box               | 2022-08-26 16:31:20.470
   11  |    Faded Color               | 2022-08-26 16:31:20.470
   12  |    Overlapping               | 2022-08-26 16:31:20.470

And this is the second table

SchoolDetail

ID  |   SchoolHeaderID | DefectClassification | Sample | Score
----|------------------|----------------------|--------|-------
   1|                 1| Overlapping          |       0|    3.0
   2|                 1| Delamination         |       0|    2.0
   5|                 1| Cracked Box          |       0|    1.5
   8|                 1| Wrong Color          |       1|    3.0
  13|                 3| Wrong Color          |       0|    3.0
  14|                 3| Burned Lamination    |       0|    1.0
  17|                 3| Misaligned Print     |       2|    1.5
  20|                 3| Paper Is Thin        |      10|    2.0
  23|                 3| Overlapping          |      11|    1.0

Now what I want is to get the maximum value in the sample column based on schoolHeaderID and used it to display a number of columns in the pivot table.

For example, I want to display the result in SchoolHeaderID=3, so this should be the query:

Declare @NoOfCol as int 
SELECT @NoOfCol=MAX(Sample) FROM SchoolDetail WHERE SchoolHeaderID=3 

The result should be 11 and the number of columns should be 12 starting from 0. The rows should be the DefectClassificationName in DefectiveTableList and the Score is based on what sample and what defect.

The result should be like this:

DefectClassificationName |  0 |  1 |  2 |  3 |  4 |  5 |  6 |  7 |  8 |  9 |  10 |  11 
-------------------------|----|----|----|----|----|----|----|----|----|----|-----|-----
Wrong Color              | 3.0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL| NULL| NULL
Paper Is Thin            |NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|  2.0| NULL
Misaligned Print         |NULL|NULL| 1.5|NULL|NULL|NULL|NULL|NULL|NULL|NULL| NULL| NULL
Defective Device         |NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL| NULL| NULL
Delamination             |NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL| NULL| NULL
Burned Lamination        | 1.0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL| NULL| NULL
Cracked Box              |NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL| NULL| NULL
Faded Color              |NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL| NULL| NULL
Overlapping              |NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL| NULL|  1.0

How to achieve this?

Answer 1

The query you want is, for example:

SELECT dtl.DefectClassificationName,
 [0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11]
FROM dbo.DefectiveTableList AS dtl
LEFT OUTER JOIN 
(
  SELECT * FROM dbo.SchoolDetail
  PIVOT (MAX(Score) FOR Sample IN (
    [0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11]
  )
  ) AS p WHERE SchoolHeaderID = @sh
) AS sd ON dtl.DefectClassificationName = sd.DefectClassification;

To get there, we can first pull the MAX(Sample), use a recursive CTE to build a comma-separated list of column names, inject those into the larger query, and then use sp_executesql.

DECLARE @SchoolHeaderID int = 3;

DECLARE @cols nvarchar(max) = N'',
  @maxCol int = (SELECT MAX(Sample) 
    FROM dbo.SchoolDetail 
    WHERE SchoolHeaderID = @SchoolHeaderID);

WITH recursiveCTE(num) AS
(
  SELECT 0 UNION ALL SELECT num+1 
  FROM recursiveCTE WHERE num < @maxCol
)
SELECT @cols += CONCAT(N',', QUOTENAME(num)) 
  FROM recursiveCTE OPTION (MAXRECURSION 32767);

DECLARE @sql nvarchar(max) = N'SELECT 
  dtl.DefectClassificationName' + @cols + N'
FROM dbo.DefectiveTableList AS dtl
LEFT OUTER JOIN 
(
  SELECT * FROM dbo.SchoolDetail
  PIVOT (MAX(Score) FOR Sample IN ('
   + STUFF(@cols, 1, 1, N'') + N'
  )) AS p
  WHERE SchoolHeaderID = @sh
) AS sd
ON dtl.DefectClassificationName = sd.DefectClassification;';

EXEC sys.sp_executesql @sql, N'@sh int', @sh = @SchoolHeaderID;

Here's an example fiddle.

On the other hand, after you changed the sample data to make it clear that SchoolHeaderID + DefectClassification was in fact not unique, the query you need is slightly different (you need to aggregate after joining):

DECLARE @sh int = 1;

WITH d AS 
(
  SELECT dtl.DefectClassificationName, sd.Score, sd.Sample
  FROM dbo.DefectiveTableList AS dtl
  INNER JOIN dbo.SchoolDetail AS sd
    ON dtl.DefectClassificationName = sd.DefectClassification
  WHERE sd.SchoolHeaderID = @sh
)
SELECT DefectClassificationName, [0],[1]
  FROM d PIVOT (MAX(Score) FOR Sample IN ([0],[1])) AS p;

And you would build this in much the same way (example fiddle):

DECLARE @SchoolHeaderID int = 1;

DECLARE @cols nvarchar(max) = N'',
  @maxCol int = (SELECT MAX(Sample) 
    FROM dbo.SchoolDetail 
    WHERE SchoolHeaderID = @SchoolHeaderID);

WITH recursiveCTE(num) AS
(
  SELECT 0 UNION ALL SELECT num+1 
  FROM recursiveCTE WHERE num < @maxCol
)
SELECT @cols += CONCAT(N',', QUOTENAME(num)) 
  FROM recursiveCTE OPTION (MAXRECURSION 32767);

DECLARE @sql nvarchar(max) = N'WITH d AS 
(
  SELECT dtl.DefectClassificationName, sd.Score, sd.Sample
  FROM dbo.DefectiveTableList AS dtl
  INNER JOIN dbo.SchoolDetail AS sd
    ON dtl.DefectClassificationName = sd.DefectClassification
  WHERE sd.SchoolHeaderID = @sh
)
SELECT DefectClassificationName' + @cols + N'
  FROM d PIVOT (MAX(Score) FOR Sample IN ('
  + STUFF(@cols, 1, 1, N'') + N')) AS p;';

EXEC sys.sp_executesql @sql, N'@sh int', @sh = @SchoolHeaderID;

That all said, why is the join to DefectiveTableList even necessary? If your Detail table contained the ID values that would make sense, but you don't have a very normalized design here, as you've repeated the strings in the detail table, which makes the join unnecessary. Just think: what are you actually getting from the other table? If there are other details we don't know, okay, but in the present state it feels like the query could be much simpler:

DECLARE @sh int = 1;

WITH d AS
(
  SELECT DefectClassification, Score, Sample
  FROM dbo.SchoolDetail AS sd
  WHERE SchoolHeaderID = @sh
)
SELECT DefectClassificationName = DefectClassification,
  [0],[1] FROM d
  PIVOT (MAX(Score) FOR Sample IN ([0],[1])) AS p;

And again we would generate it the same way (example fiddle):

DECLARE @SchoolHeaderID int = 1;

DECLARE @cols nvarchar(max) = N'',
  @maxCol int = (SELECT MAX(Sample) 
    FROM dbo.SchoolDetail 
    WHERE SchoolHeaderID = @SchoolHeaderID);

WITH recursiveCTE(num) AS
(
  SELECT 0 UNION ALL SELECT num+1 
  FROM recursiveCTE WHERE num < @maxCol
)
SELECT @cols += CONCAT(N',', QUOTENAME(num)) 
  FROM recursiveCTE OPTION (MAXRECURSION 32767);

DECLARE @sql nvarchar(max) = N'WITH d AS
(
  SELECT DefectClassification, Score, Sample
  FROM dbo.SchoolDetail AS sd
  WHERE SchoolHeaderID = @sh
)
SELECT DefectClassificationName = DefectClassification
  ' + @cols + N'
  FROM d PIVOT (MAX(Score) FOR Sample IN ('
  + STUFF(@cols, 1, 1, N'') + N')) AS p;';

EXEC sys.sp_executesql @sql, N'@sh int', @sh = @SchoolHeaderID;

Note that MAXRECURSION is only necessary if Sample can be greater than 100 (and if it can be greater than 32,767, you will have other problems generating that many columns anyway). And STRING_AGG() could be used instead of += concatenation on SQL Server 2017+.