select delimited list into one row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

select delimited list into one row

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2012-07-04 : 11:34:29

table1 :
id,name,idlist
"1","jake","1,3,6"
table2:
id,name
1,a
2,d
3,f
4,g
5,h
6,z
i want to get in the select the result:
id,name,"a+f+z"
how do i do it?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-04 : 12:24:00

Take a look at this thread - there are examples there: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-04 : 18:56:11

[code]
;With CTE
AS
(
SELECT t1.id,t1.name,t2.name as t2name
FROM table1 t1
INNER JOIN table2 t2
ON ',' + t1.idlist + ',' LIKE '%,' + CAST(id as varchar(10)) + ',%'
)

SELECT id,name,
STUFF((SELECT '+' + t2name FROM CTE WHERE id = c1.id AND name = c1.name FOR XML PATH('')),1,1,'') AS ValList
FROM (SELECT DISTINCT id,name FROM CTE) c1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources