Group results into one row 3

[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

 

[Cpreston]

Hi Chris

Firstly thank you for your patience and help.
I get a result now but it is the same result as we had before it is summing if the pact status as more than one length, I am not interested in the Pack inofrmation but just the sum of each length. Below is a snap shot of our Pack screen



So for 3.0 there is only 1 and for 3.3 there is only 4
But in the result from the query because it as a 3.0 and a 3.3 it seems to be adding them to each others total giving 3.0 = 2 and 3.3 = 5

 

[Chris Miller]

You're doing something wrong, because the pivot query I gave you creates the total simply as sum of the values in the length columns, so for example 2+5+14+9+6+8+8+4, which is 56, not 70.

 

[Cpreston]

You have missed a 14 between 9 and 6 which then gives 70

 

[Chris Miller]

Okay, my fault. But the 2 and 5 the query has for the 3.0 and 3.3 Length come from your view data, I don't manipulate that. If you expect 1 and 4 there, instead, the error is in dbo.[148-vwproductfreefeedCP], which is not under our control. Well, it may not be desired to change that view, as it already existed before your problem and therefore is meant to give the data it gives, so you'd actually need another view or query as your basis, likely.

To be precise the 2 and 5 that you see in the result are the sum of all records of the view with the same Productcode and Packstatus and Length. If you want the MAX or MIN there instead of the sum, then you simply change the SUM(Packs) to MAX(Packs) or MIN(Packs).

And if there are some other rules as to which number of the packs is relevant, then either let the view bring the correct Packs figures or mend it in any other way. For example a preprosessing that I would expect is the aggregation of a range of lengths into one lengthrange. Which you might caption as one specific length only, but it would mean a range. The pivoting will take every length as an individual column, you can only sum some concrete length not sum(3 or 3.1 or 3.2), for example.

Though what's to be expected by subsuming several lengths into one lengthrange are higher number of packs, not lower numbers. In short, either the Packs is not really the column you want to sum, or you need something that would result in smaller values than the sum, to which both MIN() and MAX() fit, but are not the only alternatives. If the rule get's overcomplicated, you can forget to do that with the given SQL aggregation functions and should work it out beforehand, not in this query, i.e. use another view as basis or better yet even the core central tables of the database.

 

[Chris Miller]

You now shouldn't just simply try MIN and MAX to see if that is better, you should know what you have find out why it doesn't fit your expectations.

So to start with, look at the data before pivoting, simply query the view as is for one ProductCode:

Select * From from dbo.[148-vwproductfreefeedCP] Where ProductCode='SSR047125DA'

double cehck, if I got that correctly from your screenshot.

The table you show from your application is listing data of a Pack Ref number, which may or may not correspsond 1:1 to a PRoductCode. Only you know that, If that's not the case you compare apples with oranges.

 

[Cpreston]

Running that I get this result All packs relate to the ProductCode



And the View as this query

SELECT         dbo.Product.ProductCode, dbo.ProductPackDetail.Length, COUNT(dbo.ProductPack.PackID) AS Packs, dbo.ProductPack.PackStatus, dbo.Product.Deleted
FROM            dbo.ProductPackDetail WITH (NOLOCK) RIGHT OUTER JOIN
                         dbo.ProductPack WITH (NOLOCK) ON dbo.ProductPackDetail.PackID = dbo.ProductPack.PackID RIGHT OUTER JOIN
                         dbo.Product WITH (NOLOCK) ON dbo.ProductPack.ProductID = dbo.Product.ProductID
WHERE        (dbo.ProductPack.PackStatus IN (10, 11, 12))
GROUP BY dbo.ProductPack.Deleted, dbo.ProductPack.BranchID, dbo.ProductPackDetail.Length, dbo.Product.ProductCode, dbo.ProductPack.PackStatus, dbo.Product.Deleted
HAVING        (dbo.ProductPack.Deleted = 0) AND (dbo.ProductPack.BranchID = 1) AND (dbo.Product.Deleted = 0) AND (dbo.Product.ProductCode = 'SSR047125DA')

 

[Chris Miller]

Well, there you have your 2 and 5, they're not even spread out into multiple records:


There's no way my or Andys query could get a 1 and 4 instead of the 2 and 5 of the view data. What you expect from us is impossible, at least with the data coming from dbo.[148-vwproductfreefeedCP]. There is no query construct like "FROM dbo.[148-vwproductfreefeedCP], but please don't join this with that, but instead with those tables".

It's just clear now, that the pivoting needs to be done on the basis of another query, not that view. But I and - I guess - Andy and Goerge, too, could only point that out and not change it to something working without knowing more about the underlying data. All I can say at this point is that given the view data it's not possible to solve your problem.

There's nothing in the view records which tells which portion of the 2 and 5 are relevant for what you want to get.

 

[Cpreston]

Ok thanks for your help and assistance many thanks

 

[Chris Miller]

You're welcome. You could try SUM(Packs+/-1. maybe 2, I don't know, ask Preston).

Just joking, but I hope you see why that's not possible to solve based on that data. I'm sure it's possible to solve - but not based on that view. Knowing the views query also doesn't help to understand the condition(s) necessary to get to the right numbers.

 

[Cpreston]

No problem, it is probably my bad View which as caused this. I will step back a bit and try and get the base figures working first, then probably apply your Pivot in some way in the end. Thanks for the patience with this and assistance.

 

[Chris Miller]

Fine, that's good to hear.

Just one more thing about my vs Andy's query. The advantage of dynamic SQL is to adjust to whatever data of Lengths you have (in this case, similar in other cases), the advantage of a view definition is the ability of SQL Server to optimize view performance, not only by indexes, but based on statistics of data, materializing a view, etc. as dyanmic SQL could differ per execution, all such optimizations are not used, though the ad hoc query you do is still optimized based on all indexes and statistics of the involved tables or views, for example.

To make this more stable and easier to maintain you could go for a legth range categorization as I already sketched, so that possible actual lengths all fall into a fixed set of ranges and do the pivoting based on that. Then you could use the code just to determine the necessary @sql one and base a stored proc on that for all future uses. It's up to you what you see as an advantage. Outliers in data could lead to a lot more columns as your application interface is prepared to display, for example. Not that it couldn't be solved by scrolling, but it might make a bad impression. I saw that you get quite long column names while one decimal is usually sufficient, the QUOTENAME(convert(varchar(50), Length)) could be changed to QUOTENAME(convert(varchar(4), Length)), for example, but you then risk that data is not summed, which doesn't match the shortened name, So perhaps better only shorten the column names as aftermath.