Query help - summarize data with some complexities - TSQL gurus needed
-
I'm not exactly sure how best to tackle this. Below is the setup. I removed columns that are inconsequential to the problem and changed the column names and values, but are otherwise indicative of the actual data. Basically, need to generate dynamic descriptions based on a summary of data within a table called InvoiceLine
--CREATE TABLE
CREATE TABLE InvoiceLine (InvoiceNumber INTEGER, FranchiseLoc VARCHAR(20)
, Distribution VARCHAR(3))
--INSERT SAMPLE DATA
INSERT INTO InvoiceLine VALUES (259,'Coastal Carolina','MJB'),(259,'Coastal Carolina','MMA'),(259,'Coastal Carolina','NEA')
,(259,'Coastal Carolina','PVA'),(259,'Lexington','CC'),(259,'Lexington','HB'),(259,'Lexington','SS'),(259,'Lexington','WB')
,(248,'Ottawa','HA'),(248,'Ottawa','HB'),(248,'Lexington','500'),(248,'Lexington','501'),(248,'Lexington','AB')
,(248,'Lexington','AK'),(248,'Lexington','BP'),(248,'Lexington','CC'),(248,'Lexington','HB'),(248,'Lexington','ILX')
,(248,'Lexington','MF'),(248,'Lexington','SS'),(248,'Lexington','WB')
--Pulling everything from the table show 21 rows
--259 and 248 are distinct values for InvoiceLineNumber
--from which a summary needs pulled
SELECT * FROM InvoiceLine
Given above data, this is what they want the resultset to look like:
SELECT 259 AS InvoiceLineNumber,'Coastal Carolina | Lexington' AS [Description], 'Coastal Carolina: MJB, MMA, NEA, PVA | Lexington: CC, HB, SS, WB' AS DescriptionDetail
UNION
SELECT 248,'Lexington | Ottawa','Lexington: 500, 501, AB, AK, BP, CC, HB, ILX, MF, SS, WB | Ottawa: HA, HB'
Basically, put unique FranchiseLoc values for a given InvoiceLineNumber into a single column called Description in a row separate by a vertical pipe. That alone isn't too difficult, but for DescriptionDetail, it gets a little crazier. Put unique FrancechiseLoc values for a InvoiceLineNumber into a single column called DescriptionDetail, but add unique distributions per FrancechiseLoc after a colon. This is where I'm getting a bit lost.
Any help/thoughts appreciated. Let me know if any questions.
-
I'm curious, why would you want to do this? I'm posting a way, but I'm bnot sure if it's the best idea to do it on SQL Server (and I'm certain that it won't be any good to store it like that).
For reference, check this article: Creating a comma-separated list[/url]
SELECT InvoiceNumber InvoiceLineNumber,
STUFF((SELECT ' | ' + FranchiseLoc
FROM #InvoiceLine b
WHERE a.InvoiceNumber = b.InvoiceNumber
GROUP BY FranchiseLoc, InvoiceNumber
ORDER BY FranchiseLoc
FOR XML PATH('')), 1, 3, '') [Description],
STUFF((SELECT ' | ' + FranchiseLoc + ':' + STUFF((SELECT ', ' + Distribution
FROM #InvoiceLine c
WHERE b.InvoiceNumber = c.InvoiceNumber
AND b.FranchiseLoc = c.FranchiseLoc
ORDER BY Distribution
FOR XML PATH('')), 1, 1, '')
FROM #InvoiceLine b
WHERE a.InvoiceNumber = b.InvoiceNumber
GROUP BY FranchiseLoc, InvoiceNumber
ORDER BY FranchiseLoc
FOR XML PATH('')), 1, 3, '') DescriptionDetail
FROM #InvoiceLine a
GROUP BY InvoiceNumber
ORDER BY InvoiceNumber DESC
-
WITH CTE AS (
SELECT InvoiceNumber, FranchiseLoc, Distribution,
ROW_NUMBER() OVER(PARTITION BY InvoiceNumber, FranchiseLoc ORDER BY Distribution) AS rn
FROM InvoiceLine)
SELECT a.InvoiceNumber,
STUFF((SELECT ' | ' + b.FranchiseLoc AS "text()"
FROM CTE b
WHERE b.InvoiceNumber = a.InvoiceNumber
AND b.rn = 1
ORDER BY b.FranchiseLoc
FOR XML PATH(''),TYPE).value('./text()[1]','VARCHAR(500)'),1,3,'') AS [Description],
STUFF((SELECT CASE WHEN c.rn = 1 THEN ' | ' + c.FranchiseLoc + ': ' ELSE ', ' END + c.Distribution AS "text()"
FROM CTE c
WHERE c.InvoiceNumber = a.InvoiceNumber
ORDER BY c.FranchiseLoc,c.rn
FOR XML PATH(''),TYPE).value('./text()[1]','VARCHAR(500)'),1,3,'') AS [DescriptionDetail]
FROM InvoiceLine a
GROUP BY a.InvoiceNumber
ORDER BY a.InvoiceNumber;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Wow, you guys are gods among men. That was quick! Thanks a lot. As to the wisdom of doing this, I agree, but in this particular case there's a requirement to display the data like this on a report. I haven't dug too much in the why part of this as I don't have much influence on the why for this.
I really need to understand recursive CTE's and FOR XML better than I do because I have a feeling a lot of what I've done could've been done easier.
-
Some people are just crazy and ask for weird things on reports.:-D
You helped a lot by posting all the ddl and sample data. With that and knowing the right techniques, the solutions comes really fast.
You might note that Mark is using some extra code in the FOR XML part. His way is safer but mine is faster (at least, it should be as tested on other solutions). You can read about that on the article I linked to find out why it's safer. And in the end it's your decition on which one you'll choose.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply