June 28, 2013 at 5:56 pm
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
June 28, 2013 at 6:24 pm
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)[/url]
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
June 28, 2013 at 6:33 pm
Thanks that worked. I need to understand how it works though. will check out the article.
June 28, 2013 at 7:50 pm
Great! If you still have questions, come back to ask.;-)
June 30, 2013 at 7:30 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 1, 2013 at 1:21 pm
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
July 1, 2013 at 6:16 pm
mickyT (7/1/2013)
HiA 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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply