show data in comma separated

Question

0.00/5 (No votes)

See more:

no name

1800 t1
1800 t2
1800 t3
1800 t4
1800 t5
1900 t6
1900 t7
1900 t8
2000 t100

I have data ( like above mentioned ) in table
what i want to do is
i want to show it to like this ( in comma separated value )

no
1800 t1,t1,t3,t4,t5
1900 t6,t7,t8
2000 t100

what the query to get result l like this

note : i have data in hundreds or even thousands. its just a sampple to clear things

Posted 15-Jul-13 19:23pm

qasimidl

Add a Solution

Comments

Reza Alipour Fard 16-Jul-13 1:38am

you data is in one column or two columns?
1900 t8 is a column or
1900 is a column and t8 is a column?

1 solution

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)

berrymaria · Accepted Answer · 2013-07-15T19:46:00

Solution 1

Hi, try this: (Note that Table_1 is your table name.)

SQL

SELECT
DISTINCT r.no,
       STUFF((SELECT DISTINCT ','+ a.name
              FROM Table_1 a
              WHERE r.no = a.no
              FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
FROM Table_1 r

I tried it by myself, and it's working.

Posted 15-Jul-13 19:46pm

berrymaria

Updated 15-Jul-13 19:47pm

v2

Comments

Reza Alipour Fard 16-Jul-13 2:03am

I vote 5 out of 5. Your solution is very short and correct. Thanks

berrymaria 16-Jul-13 2:06am

thank you! :))

zenspace 16-Jul-13 2:07am

Good answer! :D

berrymaria 16-Jul-13 2:08am

thanks! :]

Raja Sekhar S 16-Jul-13 4:02am

Nice one... +5..!