How to show the column values with a comma delimit

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 2000 Forums

SQL Server Development (2000)

How to show the column values with a comma delimit

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2006-02-28 : 10:09:56

I want to use the select statement which brings all the usernames from usernames column, i want to show all the usernames with comma delimited:

select usernames from table_users;

John,Eric,Steve,Francine etc

Please, is it possible. Thank you very much for the information.

Hamzah
Starting Member

4 Posts

Posted - 2006-02-28 : 11:10:49

R the usernames in different fields inside the table?
Like firstname, lastname, middlename?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-28 : 11:18:25

DECLARE @s VARCHAR(8000)
SELECT @s=''
SELECT @s=@s + [Name] + ','
from dbo.sysusers
where [Name] not like 'db_%'
SELECT @s = left(@s, Datalength(@s)-1)
SELECT @s

reddymade
Posting Yak Master

165 Posts

Posted - 2006-02-28 : 11:34:57

Thank you very much Srinika.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-28 : 11:40:14

Same method, fewer steps:

DECLARE @s VARCHAR(8000)
SELECT @s=isnull(@s + ', ', '') + [Name]
from dbo.sysusers
where [Name] not like 'db_%'
SELECT @s

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-01 : 01:41:40

Note that if concatenated usernames exceeds the length 8000 then you will get truncated data

Madhivanan

Failing to plan is Planning to fail

arunabh28
Starting Member

1 Post

Posted - 2006-03-01 : 01:58:15

Hi try the COALESCE function. use the following code

DECLARE @str VARCHAR(100)
SET @str=''
SELECT @str=@str+ COALESCE(USER_NAMES + ',', '')
FROM USER_TABLE
SELECT @str

Thanks and Regards
Arunabh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources