April 2, 2018 at 8:55 am
Does someone have a solution for this. If there is more than one MyId1 it needs to be in a single row with a delimiter (comma). I'm pretty sure there would never be more than 50 MyId1's for each MyId2. Appreciate any suggestions.
CREATE TABLE #Sample (MyId1 varchar(99), MyId2 int)
INSERT INTO #Sample
SELECT 111, 987 UNION ALL
SELECT 222, 987 UNION ALL
SELECT 333, 987 UNION ALL
SELECT 111, 988 UNION ALL
SELECT 222, 988 UNION ALL
SELECT 111, 989 UNION ALL
SELECT 222, 989
/*
Desired Output
MyId2 MyId1
987 111,222,333
988 111,222
989 111,222
*/
April 2, 2018 at 9:05 am
I thought I did this once before but could not find the solution. Continued to search and found it.
IF OBJECT_ID('tempdb..#FlattenIt') IS NOT NULL DROP TABLE #FlattenIt
CREATE TABLE #FlattenIt (MyId1 varchar(99), MyId2 int, MyId3 int)
INSERT INTO #FlattenIt
SELECT 111, 987, 1 UNION ALL
SELECT 222, 987, 1 UNION ALL
SELECT 333, 987, 1 UNION ALL
SELECT 111, 988, 1 UNION ALL
SELECT 222, 988, 1 UNION ALL
SELECT 111, 989, 1 UNION ALL
SELECT 222, 989, 1
SELECT * FROM #FlattenIt
--flatten
IF OBJECT_ID('tempdb..#FlattenIt2') IS NOT NULL DROP TABLE #FlattenIt2
SELECT MyId2, MyId3,
LEFT(C.Data_Values, LEN(C.Data_Values) - 1) as Flat
INTO #FlattenIt2 FROM #FlattenIt A
CROSS APPLY (SELECT CAST(MyId1 AS varchar) + ','
FROM #FlattenIt B
WHERE A.MyId2 = B.MyId2 AND A.MyId3 = B.MyId3
FOR XML PATH('') ) C ( Data_Values )
GROUP BY MyId2, MyId3, C.Data_Values
SELECT * FROM #FlattenIt2
April 3, 2018 at 3:13 am
SELECT s.MyId2,
STUFF((SELECT ',' + #Sample.MyId1 FROM #Sample WHERE s.MyId2 = #Sample.myid2 FOR XML PATH ('')), 1, 1, '' ) AS CommaSeparated
FROM #Sample AS s
GROUP BY s.myid2
April 3, 2018 at 5:03 am
Conficker - Tuesday, April 3, 2018 3:13 AMSELECT s.MyId2,
STUFF((SELECT ',' + #Sample.MyId1 FROM #Sample WHERE s.MyId2 = #Sample.myid2 FOR XML PATH ('')), 1, 1, '' ) AS CommaSeparated
FROM #Sample AS s
GROUP BY s.myid2
Can you kindly explain us how do group by works without aggregate function?
Saravanan
April 3, 2018 at 9:10 am
saravanatn - Tuesday, April 3, 2018 5:03 AMConficker - Tuesday, April 3, 2018 3:13 AMSELECT s.MyId2,
STUFF((SELECT ',' + #Sample.MyId1 FROM #Sample WHERE s.MyId2 = #Sample.myid2 FOR XML PATH ('')), 1, 1, '' ) AS CommaSeparated
FROM #Sample AS s
GROUP BY s.myid2
Can you kindly explain us how do
group byworks without aggregate function?
April 3, 2018 at 9:12 am
saravanatn - Tuesday, April 3, 2018 5:03 AMConficker - Tuesday, April 3, 2018 3:13 AMSELECT s.MyId2,
STUFF((SELECT ',' + #Sample.MyId1 FROM #Sample WHERE s.MyId2 = #Sample.myid2 FOR XML PATH ('')), 1, 1, '' ) AS CommaSeparated
FROM #Sample AS s
GROUP BY s.myid2
Can you kindly explain us how do
group byworks without aggregate function?
The FOR XML PATH is doing a "sort of" aggregation, in that it is just concatenating all the values coming out of the query, and the STUFF function is then taking that concatenated string and removing the first comma.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 3, 2018 at 5:29 pm
sgmunson - Tuesday, April 3, 2018 9:11 AMsaravanatn - Tuesday, April 3, 2018 5:03 AMConficker - Tuesday, April 3, 2018 3:13 AMSELECT s.MyId2,
STUFF((SELECT ',' + #Sample.MyId1 FROM #Sample WHERE s.MyId2 = #Sample.myid2 FOR XML PATH ('')), 1, 1, '' ) AS CommaSeparated
FROM #Sample AS s
GROUP BY s.myid2
Can you kindly explain us how do
group byworks without aggregate function?The FOR XML PATH is doing a "sort of" aggregation, in that it is just concatenating all the values coming out of the query, and the STUFF function is then taking that concatenated string and removing the first comma.
Thanks Steve
Saravanan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply