Generate Comma Separated List with SELECT statement

Today I have the following situation, where I need to display all related data in comma separated list.

Till today we are using scalar function to display Comma separated list with select statement. That scalar function use the COALESCE() to make comma separated list. Today I found wonderful solution to display comma separated list without scalar function. Let see that.

Scenario:
I have Table like:

CREATE TABLE #test(
field1 VARCHAR(5), field2 VARCHAR(5)
)

Lets insert some data in this table:

INSERT INTO #test
SELECT '001','AAA'
UNION ALL
SELECT '001','BBB'
UNION ALL
SELECT '002','CCC'
UNION ALL
SELECT '003','DDD'
UNION ALL
SELECT '004','EEE'
UNION ALL
SELECT '004','FFF'
UNION ALL
SELECT '004','GGG'

So now my table has Data like:

Get Comma separated List
Get Comma separated List

I want output like:

Get Comma separated List
Get Comma separated List

I come up with very good solution. Let me share with all of you:

SELECT field1,
 SUBSTRING( 
 (
  SELECT ( ', ' + field2)
  FROM #test t2 
  WHERE t1.Field1 = t2.Field1
  ORDER BY t1.Field1, t2.Field1
  FOR XML PATH('')
 ), 3, 1000)
FROM #test t1
GROUP BY field1
Get Comma separated List

Get Comma separated List

My Output will be:

Get Comma separated List

Get Comma separated List

Please make comments, if this helps you in any way

Advertisements

2 thoughts on “Generate Comma Separated List with SELECT statement

  1. At the same time, you also have to rein yourself in and stay mainly focused on business related topics. This also depends on your own personality, your niche and the audience you are targeting. You should not, however, blog about anything that you wouldn’t write in a business email.

    cheap air jordans

    Remember that your blog is only effective if it serves the needs of your readers and keeps them interested in your business.

    Miami Dolphins Jerseys

  2. I simply want to say I am just newbie to blogging and really liked this blog. More than likely I’m going to bookmark your blog post . You surely have very good stories. Thank you for sharing with us your blog.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s