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.

Does this answer your question? [Group by column and multiple Rows into One Row multiple columns](https://stackoverflow.com/questions/63521138/group-by-column-and-multiple-rows-into-one-row-multiple-columns) — Thom A, Feb 24 '22 at 16:36
Why? Pivoting is a UI/Reporting function typically. This can be done using a dynamic pivot and assigning a row_number() andalytic/window to each window of ingredient, brand and location. Example: https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query/10404455#10404455 but dynamic SQL comes with costs... — xQbert, Feb 24 '22 at 16:36
Are the number of rows per grouping limited to 3 or do you need to cater for any number - hence the reference to dynamic? — Stu, Feb 24 '22 at 17:09
@Stu - thanks for your question. Currently there are a maximum of five. — Davy C, Feb 25 '22 at 09:51
@Larnu Yes! Your solution there was exactly what I needed. I knew there must be something on here somewhere. I have upvoted the answer on that question you linked to. Many thanks for taking the time to write. I have now adapted that solution to my project and will post it as an answer. Best, Dave — Davy C, Feb 25 '22 at 13:47

score 0 · Answer 1 · answered Feb 25 '22 at 14:08

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!

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

1 Answers1