Group results into one row 2

[Cpreston]

Hi

I have a query that I want to display all results into one row

The query currently returns the following



But I want it to display the figures in one row for Status as Free and one row as Status Allocated with the Total being the total of all the row. I have tried group by and other suggestions but cannot seem to get the result I am after. Here is my query, could someone advise please. Thanks in advance.

SELECT        ProductCode, CASE WHEN PackStatus = 10 THEN 'Free' WHEN PackStatus = 11 THEN 'Allocated' ELSE 'Received/Reconciled' END AS Status, CASE WHEN Length = 2.4 THEN Packs ELSE NULL END AS [2.4],
                         CASE WHEN Length = 3.0 THEN Packs ELSE NULL END AS [3.0], CASE WHEN Length = 3.6 THEN Packs ELSE NULL END AS [3.6], CASE WHEN Length = 4.2 THEN Packs ELSE NULL END AS [4.2],
                         CASE WHEN Length = 4.8 THEN Packs ELSE NULL END AS [4.8], CASE WHEN Length = 5.4 THEN Packs ELSE NULL END AS [5.4], CASE WHEN Length = 6.0 THEN Packs ELSE NULL END AS [6.0],
                         Packs AS Total
FROM            dbo.[148-vwproductfreefeedCP]
GROUP BY Packs, PackStatus, Length, ProductCode

 

[Cpreston]

Thanks Chris, I ran the query and I am getting 'STRING_AGG' is not a recognized built-in function name.
I am on Microsoft SQL Server 2016 so I think the 'STRING_AGG' came in on later versions, any ideas what it was before?
Thanks

 

[Chris Miller]

You need SQL Server 2017 or higher for that. Well, then you need an XML PATH query to create the list of lengths.

 

[Cpreston]

blimey this is getting a bit to difficult for me, I will google XML PATH query to create the list of lengths

Thanks

 

[Chris Miller]

Wait for it.

Declare @Lengths varchar(100);
Declare @sql varchar(8000);

Select @Lengths = STUFF((SELECT distinct ',' + QUOTENAME(convert(varchar(50), Length)) 
            from dbo.[148-vwproductfreefeedCP]
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

Set @sql = 'SELECT PVT.*,
    CASE WHEN PackStatus = 10 THEN ''Free'' WHEN PackStatus = 11 THEN ''Allocated'' ELSE ''Received/Reconciled'' END AS Status
    FROM dbo.[148-vwproductfreefeedCP]
    PIVOT (
           SUM(Packs)
           FOR Length IN ('+@Lengths+')
          ) AS PVT';
          
execute(@sql)

You can see why STRING_AGG is much simpler, it's also faster for that use case. And most of the time XML PATH queries are done for the use case of aggregating a string.

 

[Cpreston]

Yes looks like STRIN_AGG is simpler.

Mmm I am getting this now

Msg 105, Level 15, State 1, Line 6
Unclosed quotation mark after the character string '5.100000)

 

[Chris Miller]

There's no unclosed quotation mark, are you copying this correctly?

But on the topic of quotes, for XML PATH queries, you might need to Set QUOTED_IDENTIFIER On at the top of the script.

 

[Chris Miller]

Here it's with total:

Set QUOTED_IDENTIFIER On
Declare @Lengths varchar(100);
Declare @Total varchar(1000);
Declare @sql varchar(8000);

Select @Lengths = STUFF((SELECT distinct ',' + QUOTENAME(convert(varchar(50), Length))
            from dbo.[148-vwproductfreefeedCP]
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

Set @Total = REPLACE(@Lengths,',',',0)+COALESCE(')
Set @Total = 'COALESCE('+@Total+',0)'

Set @sql = 'SELECT PVT.*,'+@Total+' as Total,
    CASE WHEN PackStatus = 10 THEN ''Free'' WHEN PackStatus = 11 THEN ''Allocated'' ELSE ''Received/Reconciled'' END AS Status
    FROM dbo.[148-vwproductfreefeedCP]
    PIVOT (
           SUM(Packs)
           FOR Length IN ('+@Lengths+')
          ) AS PVT';
          
execute(@sql)

And here's it working within sqlfiddle:

Packs Pivot

Explore our free online SQL Server editor to practice and execute SQL. AI-enhanced to chat, explain, and generate code. Start coding smarter today!

sqlfiddle.com