How to merge two tables data on behalf of id in comma separated manner in sql server 2012
-
Picture tells all what i need. Anyway i want to combine upper two tables data like below result sets. Means they should be grouped by bsns_id and its description should be comma separated taken from 2nd table. In sql server 2012.
This is the image path :
https://www.sqlservercentral/Forums/Uploads/Images/1699734-1.png
-
This article explains how to this type of thing quite easily.
http://www.sqlservercentral.com/articles/comma+separated+list/71700/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
This should work:
DECLARE @Chuff TABLE
(
Sub_Cat_ID INT NOT NULL,
BSNS_IDINT NOT NULL,
BSBS_PRFX VARCHAR(10) NOT NULL,
DO_ID INT NOT NULL
);
INSERT INTO @Chuff (Sub_Cat_ID,BSNS_ID,BSBS_PRFX,DO_ID)
VALUES (13,16,'cntcr',3),
(11,16,'cntcr',3),
(2058,1,'cntcr',3);
DECLARE @Chuff2 TABLE
(
seq INT NOT NULL,
Descr VARCHAR(25) NOT NULL
);
INSERT INTO @Chuff2 (seq,Descr)
VALUES (11,'Sparky'),(13,'Brick layer'),(2058,'Land Surveyor');
WITH Base
AS
(
SELECTC.BSNS_ID,
C1.Descr
FROM@Chuff AS C
INNER
JOIN@Chuff2 AS C1
ONC.Sub_Cat_ID = C1.seq
)
SELECTBusinessID = C.BSNS_ID,
STUFF((SELECT ',' + B.Descr
FROMBase AS B
WHEREC.BSNS_ID = B.BSNS_ID
FOR XML PATH('')), 1, 1, ''),
Prefix = C.BSBS_PRFX,
C.DO_ID
FROM@Chuff AS C
GROUPBY C.BSNS_ID,
C.BSBS_PRFX,
C.DO_ID;
Edit - Read the link in Sean's port for more details
-
Its working fine. That's what exactly i needed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply