get column data in comma[Answered] RSS

• • Reply
  Gopi.MCA

  Member

  294 Points

  679 Posts

  get column data in comma

  Jul 14, 2020 07:09 AM|Gopi.MCA|LINK

  Hello

  I have one filed Which has following Data

  Field1

  A001

  K03

  M1

  N1

  I want result set 

  'A001','K03','M1','N1'

  How to get like this data i have  more then 2000 records

  thanking you

• • Reply
  Sean Fang

  Contributor

  2870 Points

  843 Posts

  Re: get column data in comma

  Jul 14, 2020 08:29 AM|Sean Fang|LINK

  Hi Gopi.MCA,

   

  Here is a workaround with SQL query statement.

  I have below data in the database: 

  

  The sql statement is :

  Declare @Numbers AS Nvarchar(MAX) 
  SELECT  @Numbers = COALESCE(@Numbers + ',', '') + '''' +  [name] + ''''
  FROM   [city_table] 
  SELECT @Numbers

  Result:

  

   

  I understand that there are some methods like using XML and STRING_AGG. However, XML is not easier to understand and use. STRING_AGG requires SQL SERVER 2017 or later.

  If you are interested in above two methods, you could refer to here: How to concatenate text from multiple rows into a single text string in SQL server?

   

  Hope this can help you.

  Best regards,

  Sean

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.