Combine values from rows & display as string

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

General SQL Server Forums

New to SQL Server Programming

Combine values from rows & display as string

Author

Topic

H_J
Starting Member

2 Posts

Posted - 2011-09-20 : 09:44:10

Hi,

I am a SQL newbie and trying to learn as I go. I am hoping someone can help me.

I am using this basic query :
Select channelid, TPID
from channelproduct

This gives me a result as below with 2 columns:
channelid | TPID
0 | 0
1 | 25
1 | 28
2 | 3
2 | 4
2 | 27
3 | 1
and so on....

But I need the output to be as below (all TPIDs separated by comma for same channelid):
channelid | TPID
0 | 0
1 | 25, 28
2 | 3, 4, 27
3 | 1

There are around 9700+ rows with the query that I use , but if there is a possibility to combine the values under TPID for same channelid, then the number of rows would reduce to around 125 only.

Can someone please help ?

Thnx,
hj

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2011-09-20 : 10:09:45

you can use the FOR XML PATH method described here:
http://weblogs.sqlteam.com/mladenp/archive/2007/06/01/60220.aspx

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.9 out!

SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 10:43:12

[code]SELECT t.ChannelID,
STUFF((SELECT ',' + CAST (TPID AS varchar(20)) FROM Table WHERE channelid =t.channelid FOR XML PATH('')),1,1,'') AS TPID
FROM (SELECT DISTINCT channelid FROM Table)t
[/code]

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

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-20 : 10:47:38

Thanks for the article link and example guys. It is an elegant technique for these types of problems.

H_J
Starting Member

2 Posts

Posted - 2011-09-20 : 11:12:16

Thanks a lot for the replies !

I tried to use the link that spirit1 posted but I was unsuccessful (totally my fault I am sure)

@Visakh16 - your reply resolved my problem entirely! Thanks for the detailed query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 11:13:41

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources