How do I pivot a set of data based on an unknown number of columns in the resultant pivot?

Question

I have a bunch of orders for which I need to produce a report showing totals for each product group, by order.

For example:

Order Lines
Line Id, Order No., Product Id, Product  Group Id, Quantity
1      , 1        , 1         , 1                , 5
2      , 1        , 2         , 2                , 3
3      , 2        , 1         , 1                , 5
4      , 3        , 3         , 1                , 1
5      , 3        , 1         , 1                , 5
6      , 4        , 3         , 1                , 4
...
1200   , 999      , 16        , 12               , 4

Should show

Order No., Group 1, Group 2, ..., Group 12, ..., Group N
1        , 5      , 3,     , ..., 0       , ..., 0
2        , 5      , 0,     , ..., 0       , ..., 0
3        , 6      , 0,     , ..., 0       , ..., 0
4        , 4      , 0,     , ..., 0       , ..., 0
999      , 0      , 0,     , ..., 4       , ..., 0

Product groups are defined by the end user, hence I have put in the ... and Group N to signify I don't know how many columns will be in the end result.

The easy bit is grouping the order lines by product group...

SELECT OrderNo, ProductGroupId, SUM(Quantity)
FROM OrderLines
GROUP BY OrderNo, ProductGroupId

but then, how do I pivot that into the result format, when I don't know how many columns will be in the final SELECT?

I'm using SQL Server 2019.

This descriptions has helped by understanding exactly how to do the dynamic pivoting. https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server Basically you need to declare all the columns via a '@cols' and then make a SELECT '@query' with all the column names within. After that you just need to EXEC (@query) — Atmira, Nov 29 '19 at 13:42

LukStorms · Answer 1 · 2019-11-29T16:24:36.567

With N columns, where N is unknown?
That calls for a Dynamic Sql.

DECLARE @DynSql NVARCHAR(MAX);
DECLARE @Cols NVARCHAR(MAX);

--
-- Calculate the columns
--
SELECT @Cols = CONCAT(@Cols+', ', QUOTENAME(CONCAT('Group ', ProductGroupId)))
FROM OrderLines
GROUP BY ProductGroupId
ORDER BY ProductGroupId;
-- select @Cols as Cols;

--
-- Generate the query string for a pivot
--
SET @DynSql = CONCAT(N'SELECT * ',
N'FROM (',
N'SELECT OrderNo, CONCAT(''Group '', ProductGroupId) AS GroupName, SUM(Quantity) AS Quantity ',
N'FROM OrderLines ',
N'GROUP BY OrderNo, ProductGroupId) Src ',
N'PIVOT (SUM(Quantity) FOR GroupName IN (',
@Cols,
N')) Pvt ',
N'ORDER BY OrderNo');
-- select @DynSql as DynSql;

EXEC sp_executesql @DynSql;

A test on rextester here

It will show null's.

To show zero's, it's a bit more complicated.

--
-- Calculate the columns for the select
--
DECLARE @ColsSelect NVARCHAR(MAX);
SELECT @ColsSelect = CONCAT(@ColsSelect+', '+char(10), 'ISNULL(', QUOTENAME(ProductGroupId), ',0) ', QUOTENAME(CONCAT('Group ', ProductGroupId)))
FROM OrderLines
GROUP BY ProductGroupId
ORDER BY ProductGroupId;
-- select @ColsSelect as ColsSelect;

--
-- Calculate the columns for the pivot
--
DECLARE @ColsPivot NVARCHAR(MAX);
SELECT @ColsPivot = CONCAT(@ColsPivot+', ', QUOTENAME(ProductGroupId))
FROM OrderLines
GROUP BY ProductGroupId
ORDER BY ProductGroupId;
-- select @ColsPivot as ColsPivot;

--
-- Generate the query string
--
DECLARE @DynSql NVARCHAR(MAX);
SET @DynSql = CONCAT(N'SELECT OrderNo,', char(10), 
@ColsSelect, char(10),
N'FROM (', char(10),
N'SELECT OrderNo, ProductGroupId, SUM(Quantity) AS Quantity ', char(10),
N'FROM OrderLines ', char(10),
N'GROUP BY OrderNo, ProductGroupId) Src ', char(10),
N'PIVOT (SUM(Quantity) FOR ProductGroupId IN (',
@ColsPivot,
N')) Pvt ', char(10),
N'ORDER BY OrderNo');
-- select @DynSql as DynSql;

EXEC sp_executesql @DynSql;

A test on rextester here

Result:

OrderNo Group 1 Group 2 Group 12
1       5       3       0
2       5       0       0
3       6       0       0
4       4       0       0
999     0       0       4

How do I pivot a set of data based on an unknown number of columns in the resultant pivot?

1 Answers1