asked
CSV values in table
Table 1 output NAME abc bcd abnc,bcnc,dcnc asdf,fdas,dcnd abcd Table 2 Output ID NAME 1 abc 2 bcd 3 abnc 4 bcnc 5 dcnc 6 asdf 7 fdas 8 dcnd I need output in result set like below NAME IDs abc 1 bcd 2 abnc,bcnc,dcnc 3, 4, 5 asdf,fdas,dcnd 6, 7, 8
SELECT * FROM ( SELECT t3.NAME,(SELECT STUFF((SELECT ',' + CAST(ID AS varchar(20)) FROM Table1 t1 JOIN Table2 t2 ON (CHARINDEX(',' + REPLACE(t2.NAME, ' ','') + ',', ',' + REPLACE(t1.NAME, ' ','') + ',' ) > 0) WHERE t1.NAME = t3.NAME ORDER BY ID FOR XML PATH('')),1,1,''))[IDs] FROM Table1 t3)d WHERE IDs IS NOT NULL
@Ronakshah112 If it does, could you please mark Scot's answer as accepted? There is a box next to the voting buttons to the left of the question. It is visible only to the person who asked the question. The technique used by Scot to avoid an extra step of splitting the rows in table1 before joining the data with table2 is not trivial and could prove to be very useful to other site's users.
This is a pretty common problem. Here are a couple of previous answers to this question: [
http://ask.sqlservercentral.com/questions/36714/string-aggregate-functions][1] [
http://ask.sqlservercentral.com/questions/7557/changing-row-data-to-comma-delimited-data][2] [1]:
http://ask.sqlservercentral.com/questions/36714/string-aggregate-functions [2]:
http://ask.sqlservercentral.com/questions/7557/changing-row-data-to-comma-delimited-data
@KenJ It looks like OP might have either a single or comma-delimited list of names in table1 and ID and NAME in table2. This was not easy to see before I edited the question. Now it looks like the join is on the "split" values of table1 and NAME of table2 and "join" of the matches
By using above query I am not able to get my exact output. Here is the query: SELECT cast (
T.ID as varchar), STUFF((SELECT ',' + NAME FROM #tab1 T2 WHERE T2.NAME = T.NAME FOR XML PATH('')) ,1, 1, '') AS Y FROM #tab2 T Please Help to resolve this issue.
