Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql query questio Expand / Collapse
Author
Message
Posted Friday, June 28, 2013 5:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 01, 2013 11:59 AM
Points: 38, Visits: 89
I have 2 tables
Case
CaseID Name
-------------------
12341 XYZ
23451 ZZZ
90892 XXQ

CaseCode
CaseID CaseCode TypeFlag
--------------------------------
12341 001 P
12341 003 S
90892 111 S
90892 222 S
90982 999 P

Here in TypeFlag 'P' stands for PrimaryCode and 'S' for secondary code

I want an output like this
CaseID Name PrimaryCode SecondaryCode
-----------------------------------------------------------------
12341 XYZ 001 003
23451 ZZZ NULL NULL
90982 XXQ 999 111,222


I tried joining the two tables using CASE WHEN, but it generates multiple rows for a case which has more than 1 code.
Also i need comma seperated codes when there are multiple Type Flag for a caseID as shown in the last record.

Please help

thanks

kk
Post #1468748
Posted Friday, June 28, 2013 6:24 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:14 PM
Points: 2,763, Visits: 5,908
You had a good idea, but you missed the creation of the comma separated values. To learn how to do it you can read the following article. Creating a comma-separated list (SQL Spackle)

Here's a code working with your sample data. Check how I included it in a way that anyone can copy it and execute it directly in SSMS without any modification. I strongly suggest that you do this on your next posts to get better and faster help.
CREATE TABLE #Case( CaseID int, Name char(3))
INSERT #Case VALUES
(12341, 'XYZ'),
(23451, 'ZZZ'),
(90892, 'XXQ')

CREATE TABLE #CaseCode( CaseID int, CaseCode char(3), TypeFlag char(1))
INSERT #CaseCode VALUES
(12341, '001', 'P'),
(12341, '003', 'S'),
(90892, '111', 'S'),
(90892, '222', 'S'),
(90892, '999', 'P');

WITH CTE AS(
SELECT c.CaseID,
c.Name,
cc.TypeFlag,
Codes = STUFF((
SELECT ',' + CaseCode
FROM #CaseCode x
WHERE x.CaseID = cc.CaseID
AND x.TypeFlag = cc.TypeFlag
ORDER BY CaseCode
FOR XML PATH(''),
TYPE).value('.','varchar(max)'),1,1,'')
FROM #CaseCode cc
RIGHT
JOIN #Case c ON cc.CaseID = c.CaseID
)
SELECT CaseID,
Name,
MAX( CASE WHEN TypeFlag = 'P' THEN Codes END) AS PrimaryCodes,
MAX( CASE WHEN TypeFlag = 'S' THEN Codes END) AS SecondaryCodes
FROM CTE
GROUP BY CaseID,
Name

DROP TABLE #CaseCode
DROP TABLE #Case




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1468752
Posted Friday, June 28, 2013 6:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 01, 2013 11:59 AM
Points: 38, Visits: 89
Thanks that worked. I need to understand how it works though. will check out the article.
Post #1468754
Posted Friday, June 28, 2013 7:50 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:14 PM
Points: 2,763, Visits: 5,908
Great! If you still have questions, come back to ask.


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1468757
Posted Sunday, June 30, 2013 7:30 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:03 PM
Points: 3,590, Visits: 5,098
I was kind of curious how Luis's method would compare to something like this (using subqueries):

SELECT CaseID, Name
,PrimaryCodes=STUFF((
SELECT ',' + CaseCode
FROM #CaseCode b
WHERE a.CaseID = b.CaseID AND b.TypeFlag = 'P'
ORDER BY CaseCode
FOR XML PATH(''), TYPE).value('.','varchar(max)'
), 1, 1, '')
,SecondaryCodes=STUFF((
SELECT ',' + CaseCode
FROM #CaseCode b
WHERE a.CaseID = b.CaseID AND b.TypeFlag = 'S'
ORDER BY CaseCode
FOR XML PATH(''), TYPE).value('.','varchar(max)'
), 1, 1, '')
FROM #Case a


So I put it into a test harness:

CREATE TABLE #Case( CaseID int, Name varchar(8));
INSERT INTO #Case VALUES
(12341, 'XYZ'),
(23451, 'ZZZ'),
(90892, 'XXQ');

WITH Tally (n) AS (
SELECT TOP 500000 ROW_NUMBER() OVER (ORDER BY (SELECT 1))
FROM sys.all_columns a CROSS JOIN sys.all_columns b)
INSERT INTO #Case
SELECT 100000+n, 'A' + CAST(ABS(CHECKSUM(NEWID()))%1000000 AS VARCHAR(7))
FROM Tally

CREATE TABLE #CaseCode( CaseID int, CaseCode varchar(8), TypeFlag char(1));
INSERT INTO #CaseCode VALUES
(12341, '001', 'P'),
(12341, '003', 'S'),
(90892, '111', 'S'),
(90892, '222', 'S'),
(90892, '333', 'P'),
(90892, '999', 'P');

WITH Tally (n) AS (
SELECT TOP 1200000 ROW_NUMBER() OVER (ORDER BY (SELECT 1))
FROM sys.all_columns a CROSS JOIN sys.all_columns b)
INSERT INTO #CaseCode
SELECT 100000+n, 'A' + CAST(ABS(CHECKSUM(NEWID()))%1000000 AS VARCHAR(7))
,CASE WHEN ABS(CHECKSUM(NEWID()))%1000000 > 750000 THEN 'S' ELSE 'P' END
FROM Tally;

DECLARE @CaseID INT, @Name VARCHAR(8), @PrimaryCodes VARCHAR(8000)
,@SecondaryCodes VARCHAR(8000)

PRINT 'LUIS';
SET STATISTICS TIME ON;
WITH CTE AS(
SELECT c.CaseID,
c.Name,
cc.TypeFlag,
Codes = STUFF((
SELECT ',' + CaseCode
FROM #CaseCode x
WHERE x.CaseID = cc.CaseID
AND x.TypeFlag = cc.TypeFlag
ORDER BY CaseCode
FOR XML PATH(''),
TYPE).value('.','varchar(max)'),1,1,'')
FROM #CaseCode cc
RIGHT
JOIN #Case c ON cc.CaseID = c.CaseID
)
SELECT @CaseID=CaseID,
@Name=Name
,@PrimaryCodes=MAX( CASE WHEN TypeFlag = 'P' THEN Codes END) --AS PrimaryCodes,
,@SecondaryCodes=MAX( CASE WHEN TypeFlag = 'S' THEN Codes END) --AS SecondaryCodes
FROM CTE
GROUP BY CaseID,
Name
SET STATISTICS TIME OFF;

PRINT 'DWAIN';
SET STATISTICS TIME ON;
SELECT @CaseID=CaseID, @Name=Name
,@PrimaryCodes=STUFF((
SELECT ',' + CaseCode
FROM #CaseCode b
WHERE a.CaseID = b.CaseID AND b.TypeFlag = 'P'
ORDER BY CaseCode
FOR XML PATH(''), TYPE).value('.','varchar(max)'
), 1, 1, '')
,@SecondaryCodes=STUFF((
SELECT ',' + CaseCode
FROM #CaseCode b
WHERE a.CaseID = b.CaseID AND b.TypeFlag = 'S'
ORDER BY CaseCode
FOR XML PATH(''), TYPE).value('.','varchar(max)'
), 1, 1, '')
FROM #Case a
SET STATISTICS TIME OFF
DROP TABLE #CaseCode
DROP TABLE #Case


And I got these timing results:

LUIS
SQL Server Execution Times:
CPU time = 65547 ms, elapsed time = 65826 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

DWAIN
SQL Server Execution Times:
CPU time = 61922 ms, elapsed time = 62450 ms.



Looks pretty close so I'd guess actual times would depend on your real data.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1468906
Posted Monday, July 01, 2013 1:21 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:39 AM
Points: 818, Visits: 2,485
Hi

A while back I got told by someone to avoid using additional functions on the XML if possible as they can have a big impact on the performance.
For example on my machine this
SELECT @CaseID=CaseID, @Name=Name
,@PrimaryCodes=STUFF((
SELECT ',' + CaseCode
FROM #CaseCode b
WHERE a.CaseID = b.CaseID AND b.TypeFlag = 'P'
ORDER BY CaseCode
FOR XML PATH('')), 1, 1, '')
,@SecondaryCodes=STUFF((
SELECT ',' + CaseCode
FROM #CaseCode b
WHERE a.CaseID = b.CaseID AND b.TypeFlag = 'S'
ORDER BY CaseCode
FOR XML PATH('')), 1, 1, '')
FROM #Case a

runs in 11 secs rather than 34 secs. Looking at the plans for the different queries, the one above does not use a XML Reader


  Post Attachments 
plan_withoutXMLValue.jpg (2 views, 99.58 KB)
plan_withXMLValue..jpg (2 views, 198.98 KB)
Post #1469202
Posted Monday, July 01, 2013 6:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:03 PM
Points: 3,590, Visits: 5,098
mickyT (7/1/2013)
Hi

A while back I got told by someone to avoid using additional functions on the XML if possible as they can have a big impact on the performance.
For example on my machine this
SELECT @CaseID=CaseID, @Name=Name
,@PrimaryCodes=STUFF((
SELECT ',' + CaseCode
FROM #CaseCode b
WHERE a.CaseID = b.CaseID AND b.TypeFlag = 'P'
ORDER BY CaseCode
FOR XML PATH('')), 1, 1, '')
,@SecondaryCodes=STUFF((
SELECT ',' + CaseCode
FROM #CaseCode b
WHERE a.CaseID = b.CaseID AND b.TypeFlag = 'S'
ORDER BY CaseCode
FOR XML PATH('')), 1, 1, '')
FROM #Case a

runs in 11 secs rather than 34 secs. Looking at the plans for the different queries, the one above does not use a XML Reader


A very interesting point Micky! I used it for compatibility with what Luis had done. But if you don't need it because you're sure there are no special characters in the CaseCodes, then by all means do without it.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1469253
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse