output multiple rows as 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 2005 Forums

Transact-SQL (2005)

output multiple rows as one row.

Author

Topic

snomad
Starting Member

22 Posts

Posted - 2009-11-02 : 10:57:57

Hi all,
I'm having one of those days and can't for the life of me figure out how to do this within sql.

I want to:
select name from syslogins ....
and have the output be comma delimited on one line
rita, sue, bob

I'd like to do within sql as am running this on 100's on servers.

Can you help?

Thank you!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-02 : 12:54:08

An approach to adapt:

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList

Coming from : http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

No, you're never too old to Yak'n'Roll if you're too young to die.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-11-02 : 13:14:40

Here's another possible approach:


select substring(names, 3, 8000)
from   (
       select ', ' + name from sys.syslogins for xml path('')
       ) as d (names)

EDIT:
but your "hundreds of servers" need to be 2005 or later for this one.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources