January 22, 2017 at 9:18 pm
Hi,
I need a comma separated list .
CREATE TABLE #CommaSeparated
(
ID INT,
Names VARCHAR(3000)
)
INSERT INTO #CommaSeparated ( ID,Names)
SELECT 1,'John Doe' UNION
SELECT 1,' Elizabeth Jackson' UNION
SELECT 1, 'Natalie Reads' UNION
SELECT 2, 'Henny B.' UNION
SELECT 2,'Koyal B.'
SELECT * FROM #CommaSeparated
SELECT DISTINCT
ID,
stuff((
SELECT ',' + t.[Names]
FROM #CommaSeparated t
WHERE t.ID = t1.ID
ORDER BY t.[Names]
FOR xml path('')
),1,1,'') as SharedList
FROM #CommaSeparated t1
GROUP BY ID
SELECT * FROM #CommaSeparated
--Desire list
ID SharedList
1 Elizabeth Brooks,John Doe,Natalie Reads
2 Henny B.,Koyal B.
However in the original query I am getting duplicates like
1 Elizabeth Brooks,Elizabeth Brooks,John Doe,John Doe,Natalie Reads,Natalie Reads
Thanks,
PSB
January 23, 2017 at 12:43 am
PSB - Sunday, January 22, 2017 9:18 PMHi,
I need a comma separated list .CREATE TABLE #CommaSeparated
(
ID INT,
Names VARCHAR(3000)
)
INSERT INTO #CommaSeparated ( ID,Names)
SELECT 1,'John Doe' UNION
SELECT 1,' Elizabeth Jackson' UNION
SELECT 1, 'Natalie Reads' UNION
SELECT 2, 'Henny B.' UNION
SELECT 2,'Koyal B.'
SELECT * FROM #CommaSeparated
SELECT DISTINCT
ID,
stuff((
SELECT ',' + t.[Names]
FROM #CommaSeparated t
WHERE t.ID = t1.ID
ORDER BY t.[Names]
FOR xml path('')
),1,1,'') as SharedList
FROM #CommaSeparated t1
GROUP BY ID
SELECT * FROM #CommaSeparated
--Desire list
ID SharedList
1 Elizabeth Brooks,John Doe,Natalie Reads
2 Henny B.,Koyal B.However in the original query I am getting duplicates like
1 Elizabeth Brooks,Elizabeth Brooks,John Doe,John Doe,Natalie Reads,Natalie ReadsThanks,
PSB
In your code, you have a GROUP BY, which means that you don't need the DISTINCT.
However, your data appears to have duplicates - This can be simulated by executing this part of your sample code twice
INSERT INTO #CommaSeparated ( ID,Names)
SELECT 1, 'John Doe' UNION
SELECT 1,' Elizabeth Jackson' UNION
SELECT 1, 'Natalie Reads' UNION
SELECT 2, 'Henny B.' UNION
SELECT 2, 'Koyal B.';
Now, you need to de-duplicate your data before creating the CSV string.
SELECT
ID,
STUFF((
SELECT ',' + t.[Names]
FROM (SELECT DISTINCT t2.[Names]
FROM #CommaSeparated t2
WHERE t2.ID = t1.ID
) AS t
ORDER BY t.[Names]
FOR xml path('')
),1,1,'') as SharedList
FROM #CommaSeparated t1
GROUP BY ID;
January 23, 2017 at 2:43 am
Just a quick thought, you should add the TYPE directive and the value method if there are any characters within the concatenated text that would be encoded in the FOR XML statement such as & < > etc.
😎
SELECT
ID,
STUFF((
SELECT ',' + t.[Names]
FROM (SELECT DISTINCT t2.[Names]
FROM #CommaSeparated t2
WHERE t2.ID = t1.ID
) AS t
ORDER BY t.[Names]
FOR xml path(''),TYPE
).value('(./text())[1]','VARCHAR(MAX)'),1,1,'') as SharedList
FROM #CommaSeparated t1
GROUP BY ID;
January 23, 2017 at 4:27 am
Eirikur Eiriksson - Monday, January 23, 2017 2:43 AMJust a quick thought, you should add the TYPE directive and the value method if there are any characters within the concatenated text that would be encoded in the FOR XML statement such as & < > etc.
😎
SELECT
ID,
STUFF((
SELECT ',' + t.[Names]
FROM (SELECT DISTINCT t2.[Names]
FROM #CommaSeparated t2
WHERE t2.ID = t1.ID
) AS t
ORDER BY t.[Names]
FOR xml path(''),TYPE
).value('(./text())[1]','VARCHAR(MAX)'),1,1,'') as SharedList
FROM #CommaSeparated t1
GROUP BY ID;
Thank you Eirikur. Nice tip.
January 23, 2017 at 6:53 am
Thanks All.
January 25, 2017 at 3:32 pm
Try not to use DISTINCT.
Ever.
Just forget this keyword exists
As for this query - keep in mind, that "FOR XML" query is a separate correlated subquery, grouping applied to the external query does not apply to it.
it needs its own "GROUP BY", if you want unique values.
SELECT ID,
stuff((
SELECT ',' + t.[Names]
FROM #CommaSeparated t
WHERE t.ID = t1.ID
GROUP BY t.[Names]
ORDER BY t.[Names]
FOR xml path(''),TYPE
).value('.','VARCHAR(MAX)'),1,1,'') as SharedList
FROM #CommaSeparated t1
GROUP BY ID
_____________
Code for TallyGenerator
January 25, 2017 at 3:37 pm
What a bizarre claim. DISTINCT is more efficient than GROUP BY in given situations, so you absolutely shouldn't forget it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 25, 2017 at 7:39 pm
I wonder - what would be those "given situations"?
_____________
Code for TallyGenerator
January 25, 2017 at 9:14 pm
Sergiy - Wednesday, January 25, 2017 3:32 PMTry not to use DISTINCT.
Ever.
Just forget this keyword exists
As for this query - keep in mind, that "FOR XML" query is a separate correlated subquery, grouping applied to the external query does not apply to it.
it needs its own "GROUP BY", if you want unique values.
SELECT ID,
stuff((
SELECT ',' + t.[Names]
FROM #CommaSeparated t
WHERE t.ID = t1.ID
GROUP BY t.[Names]
ORDER BY t.[Names]
FOR xml path(''),TYPE
).value('.','VARCHAR(MAX)'),1,1,'') as SharedList
FROM #CommaSeparated t1
GROUP BY ID
What's wrong with DISTINCT?
-- Itzik Ben-Gan 2001
January 25, 2017 at 11:36 pm
Alan.B - Wednesday, January 25, 2017 9:14 PMSergiy - Wednesday, January 25, 2017 3:32 PMTry not to use DISTINCT.
Ever.
Just forget this keyword exists
As for this query - keep in mind, that "FOR XML" query is a separate correlated subquery, grouping applied to the external query does not apply to it.
it needs its own "GROUP BY", if you want unique values.
SELECT ID,
stuff((
SELECT ',' + t.[Names]
FROM #CommaSeparated t
WHERE t.ID = t1.ID
GROUP BY t.[Names]
ORDER BY t.[Names]
FOR xml path(''),TYPE
).value('.','VARCHAR(MAX)'),1,1,'') as SharedList
FROM #CommaSeparated t1
GROUP BY IDWhat's wrong with DISTINCT?
It works with the set generated by SELECT.
2 queries doing the same thing:
SET STATISTICS IO ON
SELECT DISTINCT ID,
stuff((
SELECT ',' + t.[Names]
FROM #CommaSeparated t
WHERE t.ID = t1.ID
GROUP BY t.[Names]
ORDER BY t.[Names]
FOR xml path('')
),1,1,'') as SharedList
FROM #CommaSeparated t1
SELECT ID,
stuff((
SELECT ',' + t.[Names]
FROM #CommaSeparated t
WHERE t.ID = t1.ID
GROUP BY t.[Names]
ORDER BY t.[Names]
FOR xml path('')
),1,1,'') as SharedList
FROM #CommaSeparated t1
GROUP BY ID
SET STATISTICS IO OFF
Here is the difference:Table 'Worktable'. Scan count 9, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#CommaSeparated_____________________________________________________________________________________________________000000000415'. Scan count 3, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#CommaSeparated_____________________________________________________________________________________________________000000000415'. Scan count 3, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
_____________
Code for TallyGenerator
January 26, 2017 at 8:42 pm
Alan.B - Wednesday, January 25, 2017 9:14 PMSergiy - Wednesday, January 25, 2017 3:32 PMTry not to use DISTINCT.
Ever.
Just forget this keyword exists
As for this query - keep in mind, that "FOR XML" query is a separate correlated subquery, grouping applied to the external query does not apply to it.
it needs its own "GROUP BY", if you want unique values.
SELECT ID,
stuff((
SELECT ',' + t.[Names]
FROM #CommaSeparated t
WHERE t.ID = t1.ID
GROUP BY t.[Names]
ORDER BY t.[Names]
FOR xml path(''),TYPE
).value('.','VARCHAR(MAX)'),1,1,'') as SharedList
FROM #CommaSeparated t1
GROUP BY IDWhat's wrong with DISTINCT?
There's nothing wrong with DISTINCT. 😉 There's a lot wrong with the way people use it because they don't think about the differences between DISTINCT and GROUP BY. See the following article for a very old classic example... http://www.sqlservercentral.com/articles/Test+Data/61572/
Sergiy did hit it out of the park with his example above. The article explains the difference.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply