T-SQL Pivot table rows into dynamic columns per count of instances

Question

Given a table thus:

ID	Ingredient	Brand	KitchenLocation	PurchaseSize	PurchaseUnit	InStockA	InStockB
14	Beans, Pinto	Chefs Quality	Pantry	55	oz	15	60
15	Beans, Pinto	Chefs Quality	Pantry	108	oz	2	7
16	Beans, Pinto	Chefs Quality	Pantry	648	oz	1	5
17	Beans, Pinto	First Street	Pantry	15	oz	23	10
18	Beans, Pinto	First Street	Pantry	40	oz	5	1
19	Beans, Pinto	Sun Vista	Pantry	29	oz	0	100

I am hoping to find a way of pivoting so the output is grouped by Ingredient, Brand, and KitchenLocation. So the PurchaseSize, PurchaseUnit, InStockA, and InStockB values display as dynamic columns inside this grouping:

Ingredient	Brand	KitchenLocation	Size1	Unit1	InStock1A	InStock1B	Size2	Unit2	InStock2A	InStock2B	Size3	Unit3	InStock3A	InStock3B
Beans, Pinto	Chefs Quality	Pantry	55	oz	15	60	108	oz	2	7	648	oz	1	5
Beans, Pinto	First Street	Pantry	15	oz	23	10	40	oz	5	1
Beans, Pinto	Sun Vista	Pantry	29	oz	0	100

I can't find an exact question here on Stackoverflow, though of course some are similar. I started to put this code together, but I am stuck, firstly, because Ingredient comes back as NULL in all cases, and secondly I don't know how to dynamically get the new rows to display with a calculated number in the column name (eg. InStock1A, InStock2A etc.):

SELECT Ingredient, Brand, KitchenLocation, InStockA, InStockB
FROM
(SELECT Ingredient AS EIngredient, Brand, KitchenLocation, PurchaseSize, InStockA, InStockB
    FROM tmpHoldingTable) AS Source_Table
        PIVOT
        (MAX(PurchaseSize)
        FOR
        EIngredient IN (Ingredient)
) AS PIVOT_TABLE

Can anyone point me in the right direction please? Many thanks in advance.

Answer 1

Many thanks to @Larnu for pointing me towards something I could adapt. The answer is thus:

DECLARE @qry nvarchar(MAX),
        @crlf nchar(2) = NCHAR(13) + NCHAR(10),
        @maxTally int;
SELECT @maxTally = MAX(C)
FROM (SELECT COUNT(*) AS C
      FROM tmpHoldingTable
      GROUP BY Ingredient, Brand, KitchenLocation) R;
WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (@maxTally) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2) --100 rows, add more Ns for more rows
SELECT @qry = N'WITH RNs AS(' + @crlf +
              N'    SELECT Ingredient,' + @crlf +
              N'           Brand,' + @crlf +
              N'           KitchenLocation,' + @crlf +  
              N'           PurchaseSize,' + @crlf +
              N'           PurchaseUnit,' + @crlf +
              N'           InStockA,' + @crlf +
              N'           InStockB,' + @crlf +
              N'           ROW_NUMBER() OVER (PARTITION BY Ingredient, Brand, KitchenLocation ORDER BY (SELECT ID)) AS RN ' + @crlf +
              N'    FROM tmpHoldingTable)' + @crlf +
              N'SELECT Ingredient,' + @crlf +
              N'       Brand,' + @crlf +
              N'       KitchenLocation,' + @crlf +

              STUFF((SELECT N',' + @crlf +
                            CONCAT(N'       MAX(CASE RN WHEN ',T.I,N' THEN PurchaseSize END) AS PurchaseSize',T.I)
                     FROM Tally T
                     ORDER BY T.I ASC
                     FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,3,N'') + ',' + @crlf +

              STUFF((SELECT N',' + @crlf +
                            CONCAT(N'       MAX(CASE RN WHEN ',T.I,N' THEN PurchaseUnit END) AS PurchaseUnit',T.I)
                     FROM Tally T
                     ORDER BY T.I ASC
                     FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,3,N'') + ',' + @crlf +

              STUFF((SELECT N',' + @crlf +
                            CONCAT(N'       MAX(CASE RN WHEN ',T.I,N' THEN InStockA END) AS InStockA',T.I)
                     FROM Tally T
                     ORDER BY T.I ASC
                     FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,3,N'') + ',' + @crlf +
              STUFF((SELECT N',' + @crlf +
                            CONCAT(N'       MAX(CASE RN WHEN ',T.I,N' THEN InStockB END) AS InStockB',T.I)
                     FROM Tally T
                     ORDER BY T.I ASC
                     FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,3,N'') + @crlf +

              N'FROM RNs R' + @crlf +
              N'GROUP BY Ingredient,' + @crlf +
              N'         Brand, ' + @crlf +
              N'         KitchenLocation;';

EXEC sys.sp_executesql @qry;

We put all the fields we need in the RNs WITH statement, making sure to group down by the three grouping fields. We then use the STUFF function on each of the columns that we want to dynamically display. The only downside is that the columns come out not in the exact desired order, but that can be fixed by placing the output into a separate holding table and then utilising a SELECT from this where the columns are placed in the correct order and returned. Many thanks @Larnu!