SQL convert 1 column's values to 1 row string

  • 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.

  • 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

  • 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!

  • 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

  • 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

  • Thank You, found a solution:

    DECLARE @STR varchar(500)

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

    FROM dbo.fcUser

    SELECT @STR

  • 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

  • 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
  • Hi,

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

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

    Regards

  • 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
  • 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

  • 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

  • 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
  • How do i see 90 or 80?

  • 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

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

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