SQL convert 1 column's values to 1 row string

Question

Post reply

SQL convert 1 column's values to 1 row string

ismail casper

SSCrazy

Points: 2003
More actions
April 18, 2009 at 9:55 am

#129412

Hi, I need help please.
I have one column whith different values depending on result.
I would like to return a one row string result with a comma seperating the values.
The column output as follows:
Column A
--------
1
2
3
I would like to output as 1, 2, 3
Please Assist.

Viewing 15 posts - 1 through 15 (of 19 total)

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

This is a fairly common question. Various solutions are posted here:

http://www.sqlservercentral.com/Forums/Topic695359-338-1.aspx#bm695486

Take a look at the FOR XML solution near the front, and please let me know if you have any questions. 🙂

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

ismail casper SSCrazy Points: 2003 More actions · Answer 2

Thank You but unfortunately very complicated, i thought of a case but the values differs for every run & don't know what the values would be.

Any other ideas!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

Sorry if the entire thread was too intimidating, but the FOR XML technique doesn't require that you know the values in advance.

Here it is applied to your example. Let me know if you have any questions.

;with someTable (col_A) as-- just using this cte as an example table

(select 1 union all

select 2 union all

select 3 union all

select 4

)

-- the STUFF is used to get rid of the first comma, which will be at position 1 within the string

select stuff((

SELECT ',' + cast(col_A as varchar(20))

FROM someTable

ORDER BY col_A

FOR XML PATH('')

),1,1,'') as A_Values

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

ismail casper SSCrazy Points: 2003 More actions · Answer 4

Thank You for help & apologies for the spoonfeeding.

The query returns different amount of rows & i think the union all wont tork as i don't know how much rows or values would be returned.

The query is to return values depending on a user logged in.

I tried the below but not luck - does not like the "FOR"

select stuff((

SELECT ',' + cast(RoleID as varchar(20))

FROM dbo.fcUser

ORDER BY RoleID

FOR XML PATH('')

),1,1,'') as A_Values

ismail casper SSCrazy Points: 2003 More actions · Answer 5

Thank You, found a solution:

DECLARE @STR varchar(500)

SELECT @STR=COALESCE(@Str,'') + CAST(ID as varchar(10)) + ','

FROM dbo.fcUser

SELECT @STR

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

Glad you found a solution. Good luck.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 7

Hi ,

I'm interested to know why bob's solution didn't work?

What version of SQL are you using?

Please note the the "working" solution that ismail gave you return with a trailing comma...

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

ismail casper SSCrazy Points: 2003 More actions · Answer 8

Hi,

I'm using SQL2005, the query did not like the "FOR'

to remove the last comma: SELECT LEFT(@Str,LEN(@str)-1)

Regards

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 9

very strange, I ran that code on SQL 2005 Compatibility 90 and it worked a charm, in fact it's my preferred solution.

what was the actual code you ran and the actual error?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 10

There must be a typo somewhere. That query is valid for 2005. Could you cut and paste your code back here, along with the error?

Putting the comma at the front and using STUFF instead of LEFT is more efficient. The system doesn't have to calculate lengths because you know the extra comma is always going to be in the first position.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

ismail casper SSCrazy Points: 2003 More actions · Answer 11

Hi, I tried again.

select stuff((

SELECT ',' + cast(RoleID as varchar(20))

FROM dbo.fcUser

ORDER BY RoleID

FOR XML PATH('')

),1,1,'') as A_Values

error: Msg 170, Level 15, State 1, Line 5

Line 9: Incorrect syntax near 'XML'.

Regards

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 12

still no error on my side.

Are you sure you using compatability 90 and not 80?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

ismail casper SSCrazy Points: 2003 More actions · Answer 13

ismail casper

SSCrazy

Points: 2003

April 20, 2009 at 10:24 am

#980848

How do i see 90 or 80?

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 14

In SSMS Right click the db, properties then on the options tab, you will see at the top Compatibility level:

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life