October 26, 2016 at 3:30 pm
Hello,
I am trying to group multiple Rows into 1 string based on ID field. Here is my sample code:
CREATE TABLE #MyLocs (RegionID int, Loc varchar(10))
INSERT INTO #MyLocs (RegionID, Loc) VALUES (1, 'A101')
INSERT INTO #MyLocs (RegionID, Loc) VALUES (1, 'A102')
INSERT INTO #MyLocs (RegionID, Loc) VALUES (1, 'A103')
INSERT INTO #MyLocs (RegionID, Loc) VALUES (2, 'B101')
INSERT INTO #MyLocs (RegionID, Loc) VALUES (2, 'B102')
INSERT INTO #MyLocs (RegionID, Loc) VALUES (3, 'C101')
INSERT INTO #MyLocs (RegionID, Loc) VALUES (3, 'C102')
INSERT INTO #MyLocs (RegionID, Loc) VALUES (3, 'C103')
INSERT INTO #MyLocs (RegionID, Loc) VALUES (4, 'D101')
INSERT INTO #MyLocs (RegionID, Loc) VALUES (4, 'D102')
INSERT INTO #MyLocs (RegionID, Loc) VALUES (5, 'E101')
INSERT INTO #MyLocs (RegionID, Loc) VALUES (5, 'E102')
INSERT INTO #MyLocs (RegionID, Loc) VALUES (5, 'E103')
INSERT INTO #MyLocs (RegionID, Loc) VALUES (5, 'E104')
INSERT INTO #MyLocs (RegionID, Loc) VALUES (5, 'E105')
INSERT INTO #MyLocs (RegionID, Loc) VALUES (6, 'F101')
--SELECT * FROM #MyLocs
The outcome I getting is grouping by RegionID, but is not putting the Loc in the correct Regions. Here is my current query:
SELECT RegionID,
STUFF((
SELECT ', ' + Loc FROM #MyLocs
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1,1,'') As strLoc
FROM #MyLocs
GROUP BY RegionID
The outcome I am hoping for would be:
RegionID 1: A101,A102, A103
RegionID 2: B101,B102
RegionID 3: C101,C102,C103
RegionID 4: D101,D102
RegionID 5: E101,E102,E103,E104,E105
RegionID 6: F101
I am able to get close by doing the following, but am unable to group the string based on Region.
Can someone help me understand how to group rows into one based on the RegionID?
October 26, 2016 at 3:58 pm
I made a couple of small changes and it seems to work with your dataset
SELECT l2.RegionID,
STUFF(
(SELECT ',' + l1.Loc
FROM #MyLocs l1
where l1.regionID = l2.regionID
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') strloc
FROM #MyLocs l2
GROUP BY l2.RegionID
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 26, 2016 at 4:13 pm
Ahhhh! Got it! thank you very much! I was missing the join!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply