Convert column values into single row comma separator

Question

Convert column values into single row comma separator

Rock

Old Hand

Points: 367
More actions
February 17, 2020 at 5:45 am
Go to Answer
#3726312
Team
My requirement is to convert column values into single row comma separator along with single quotes. Pls help
ServerName > Column Name
Values in a row like below
Server1
server2
server3
server44
I need a result like
'Server1,server2,server3,server44'
- This topic was modified 4 years, 9 months ago by Rock.

Rock

Old Hand

Points: 367
More actions
February 18, 2020 at 9:00 am
Answer
#3726670

Issue Resolved
DECLARE @Hostnames VARCHAR(max)
Select @Hostnames = concat('''', stuff((select ', ' + ServerName from #serverinput for xml path ('')), 1, 2, ''), '''');
select @Hostnames

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

SELECT ServerName + ', ' AS 'data()'
FROM ServerList
FOR XML PATH('');

Rock Old Hand Points: 367 More actions · Answer 2

It doesnt work.

The o/p should be like this

'Server1,server2,server3'

The current result is

Server1, server2, server3

Steve Collins SSCrazy Points: 2157 More actions · Answer 3

pietlinden wrote:

SELECT ServerName + ', ' AS 'data()'
FROM ServerList
FOR XML PATH('');

That's nice. I've only ever seen string aggregation (prior to Sql Server 2016) done with the the 'for xml' and stuff() function combination. The downside in the case is an extra comma at the end of the string which needs to be removed.

Rock, the encompassing quotes need to be escaped because the tick mark is a reserved symbol in Sql Server. A single tick mark when escaped is 4 ticks ''''

/* xml data() column */
;with
server_cte(ServerName) as (select 'Server1' union all select 'Server2'),
servers_cte(ServersName) as (SELECT ServerName + ', ' AS 'data()' FROM server_cte FOR XML PATH(''))
select
  concat('''', left(sc.ServersName, (len(sc.ServersName)-1)), '''')
from
  servers_cte sc;

/* xml + stuff function combo */
;with
server_cte(ServerName) as (select 'Server1' union all select 'Server2')
select 
  concat('''', stuff((select ', ' + ServerName from server_cte for xml path ('')), 1, 2, ''), '''');

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

Never mind. Misread something.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Collins SSCrazy Points: 2157 More actions · Answer 5

Removed

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2157 More actions · Answer 6

Ok cool

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 7

Steve Collins wrote:

Oh wait Jeff you changed it. Ok cool

Yeah... my bad. I've only got 2 cups of coffee under my belt and totally misread the code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rock Old Hand Points: 367 More actions · Answer 8

how do i set this to a local varabile in sql server

I used this, it doesnt work.

Declare @localvariable varchar(max)

;with

server_cte(ServerName) as (select 'Server1' union all select 'Server2')

select

concat('''', stuff((select ', ' + ServerName from server_cte for xml path ('')), 1, 2, ''), '''');

Select @localvariable = select count(*) from server_cte

Error: Invalid Object Name server_cte MSG 208

pietlinden SSC Guru Points: 63503 More actions · Answer 9

pietlinden

SSC Guru

Points: 63503

February 18, 2020 at 4:55 am

#3726638

Select @localvariable = count(*) from server_cte

Rock Old Hand Points: 367 More actions · Answer 10

Rock

Old Hand

Points: 367

February 18, 2020 at 5:37 am

#3726643

Invalid object name 'server_cte'

pietlinden SSC Guru Points: 63503 More actions · Answer 11

Wait wait wait...

you can only reference a CTE immediately following the declaration. Why do you need a CTE for this at all? What are you trying to accomplish?

jbowers Old Hand Points: 369 More actions · Answer 12

I realize you've already accepted an answer, but I'd like to offer an alternative solution that doesn't have anything to do with XML.

/*the table where your data resides (I will use a table variable)*/
DECLARE @temp AS TABLE(ServerName nvarchar(10) PRIMARY KEY);
INSERT INTO @temp(ServerName)
VALUES ('Server1'),
('server2'),
('server3'),
('server44');

/*loop through the rows and create the comma-separated string*/
DECLARE @str AS nvarchar(max) = NULL;
SELECT @str = COALESCE(@str + ',' + ServerName, ServerName)
FROM @temp;

/*add the leading and trailing quotes*/
SELECT '''' + @str + '''';

This reply was modified 4 years, 9 months ago by jbowers. Reason: removed an apostrophe from the first comment, which was throwing off the formatting
This reply was modified 4 years, 9 months ago by jbowers.