how to convert rows into comma separated values in sql server 2008?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

hi ,

I have one table like

SQL

create table test(id int)
insert into test(101),(102),(103).....

now my expected output is:

test
101,102,103

I know one method to resolve my problem :

SQL

decare @id varchar(100)
select @id=coalsce(@id,'')+cast(id as varchar) from test
select @test

but the above method is not suitable to my requirement
so i need to resolve it with normal select statement i.e without using declared variable

Posted 28-Dec-12 2:47am

FranklinRemo

Updated 28-Dec-12 3:11am

v3

Add a Solution

Comments

Sandeep Mewara 28-Dec-12 8:59am

Your question is not clear even after an example. Please re-word and try to share what are you trying to do?

__TR__ 28-Dec-12 9:15am

Comment from OP
"hi sandeep where you find diffuclty in my post. no i added some more points just check it once"

3 solutions

Solution 4

my actual requirement is differnet

finaly i solved by taking the above examples

my final solution is

SQL

select
    LEFT(SubGroupId ,LEN(SubGroupId ) - 1)
from
    (
    select
        CONVERT(varchar(max),
            (
                select SubGroupId  as [text()], ',' as [text()]


                from CMSClientSubGroup  csg WITH(NOLOCK)
            INNER JOIN USER_ACCOUNT ua WITH(NOLOCK)
            --  on u.user_id=ua.user_id
            --INNER JOIN CMSClientSubGroup csg WITH(NOLOCK)
                on csg.SubGroupId=ua.account_id
            --WHERE  ua.USER_ID =1
                for xml path('')

            )

        )as result
    ) s

Posted 28-Dec-12 6:32am

FranklinRemo

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

__TR__ · Accepted Answer · 2012-12-28T03:23:00

Solution 2

Try

SQL

create table #test(id int)
insert into #test(id) VALUES (101)
insert into #test(id) VALUES (102)
insert into #test(id) VALUES (103)

SELECT SUBSTRING(
(
SELECT ',' + CAST(Id AS VARCHAR) FROM #Test
FOR XML PATH('')), 2,10000) AS Csv

DROP TABLE #test

Posted 28-Dec-12 3:23am

__TR__

Comments

Wendelius 28-Dec-12 9:26am

Good example, 5.

__TR__ 28-Dec-12 9:28am

Thank you.

Member 12431701 5-Dec-19 8:02am

Try this example...i hope it will help

SELECT STUFF((SELECT ', ' + Column Name FROM Table Name FOR XML PATH('')),1,2,'') as [Column Name]

Member 4193117 31-Aug-20 7:50am

how to select other columns if at there in a table , how to select them as well,
can it be done with group by?

Wendelius · Accepted Answer · 2012-12-28T03:25:00

Solution 3

Probably the easiest way to select values into a comma separated list is to use FOR XML clause. For example have a look at this article: Get Column values as comma seperated string [^].

If you need a lot more functionality, you can even consider creating a custom aggregate. See chapter Using Multiple Parameters, Concatenate in Custom Aggregates in SQL Server[^]

Posted 28-Dec-12 3:25am

Wendelius

Comments

__TR__ 28-Dec-12 9:27am

My 5!

Wendelius 28-Dec-12 9:28am

Thanks :D

Espen Harlinn 29-Dec-12 7:39am

Nice :-D

Wendelius 29-Dec-12 7:53am

Thanks :D