How to display comma seperated in one column

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

How to display comma seperated in one column

Author

Topic

rams.enjamuri
Starting Member

2 Posts

Posted - 2011-10-26 : 17:10:35

Table1

Table1Id Table1Name Table1Phone
501 AAAAA 1111111111
502 BBBBB 2222222222
503 CCCCC 3333333333
----------------------------------
Table2

Table2Id Table2Name
1 abc
2 def
3 ghi

----------------------------------
Table3

Table1Id Table2Id
501 1
501 2
501 3
502 1
502 2
503 1
503 3
----------------------------------
output as below

Table1Name Table2Name Table1Phone
AAAAA abc, def, ghi 1111111111
BBBBB abc, def 2222222222
CCCCC abc, ghi 3333333333

I want to display with comma seperte in front end(gridview), is possible with a query and bind to gridview or get the values and do some coding in c#

could anyone help in this regard

Thanks in advance!!!!!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-26 : 18:35:29

refer to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 02:37:13

[code]
SELECT t1.Table1Name,
STUFF((SELECT ',' + Table2Name FROM Table2 WHERE Table2Id = t3.Table2Id ORDER BY Table2Id FOR XML PATH('')),1,1,'') AS Table2Names,
t1.Table1Phone
FROM Table3 t3
INNER JOIN Table1 t1
ON t1.Table1Id = t3.Table1Id
[/code]

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

rams.enjamuri
Starting Member

2 Posts

Posted - 2011-10-27 : 10:09:59

quote:
Originally posted by visakh16

SELECT t1.Table1Name,
STUFF((SELECT ',' + Table2Name FROM Table2 WHERE Table2Id = t3.Table2Id ORDER BY Table2Id FOR XML PATH('')),1,1,'') AS Table2Names,
t1.Table1Phone
FROM Table3 t3
INNER JOIN Table1 t1
ON t1.Table1Id = t3.Table1Id

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

I am sorry!!!

I forgot to mention, I am using DB2 not SQL server

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-27 : 10:15:42

Try a db2 forum then.
You get CTEs in DB2 which can be used in a similar way.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources