SQL SERVER – Create a Comma Delimited List Using SELECT Clause From Table Column

I received following question in email : How to create a comma delimited list using SELECT clause from table column?

Answer is to run following script.

USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM Production.Product
SELECT @listStr
GO

SQL SERVER - Create a Comma Delimited List Using SELECT Clause From Table Column notes85-800x536

I have previously written a similar article where I have demonstrated this method using three sample examples. You can read the article for further information. SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP

Here are few of the interesting articles related to this blog post:

Simple CSV implementations may prohibit field values that contain a comma or other special characters such as CSV. The CSV file format is not standardized. CSV is a common data exchange format that is widely supported by consumer, business, and scientific applications. CSV formats are best used to represent sets or sequences of records in which each record has an identical list of fields.

Reference : Pinal Dave (https://blog.sqlauthority.com)

CSV, Database, SQL Function, SQL Scripts, SQL Server, SQL Stored Procedure
Previous Post
SQL SERVER – Example of DISTINCT in Aggregate Functions
Next Post
SQL SERVER – Compound Assignment Operators – A Simple Example

Related Posts

109 Comments. Leave new

  • Deepak Bhargava
    May 5, 2012 12:21 am

    I am having same problem which Steve has mentioned above. Is anyone know the solution of this problem ?

    Reply
  • Hi Pinal,
    I have a problem trying to resolve this since two days but no use , could you please help me on this

    Source table

    Id Product

    111 BCM123

    111 BCM456

    111 BCM789

    222 BCM123

    222 BCM456

    222 BCM789

    I am looking for the output like this

    Id Product

    111 BCM123,BCM456,BCM789
    222 BCM123,BCM456,BCM789

    Thanks in advance

    Reply
  • Hi all,

    Wanted to know if we can get comma separated list for 1 column and all get rest columns w/o commas in a query having multiple joins?

    Thanks,

    Reply
  • hello Pinal,

    I have a requirement where a variable with comma separated value is passed as a parameter to a SP and those comma separated values are the field names.Here I want to display only the columns mentioned in the input paramenter:

    Ex:-
    Table1 is a table with following columns
    Col1
    Col2
    Col3
    Col4
    Col5
    Col6
    Col7
    Col8

    @Param=’Col1,Col2,Col3,Col5,Col8′ — this is the parameter that wl be input to SP.

    and I want the above mentioned columns to be displayed when the SP is executed.

    Thanks in Advance

    Reply
  • how can i display idcount in message box ? i am using Linq to sql and this is my stored procedure.. please replyy……

    ALTER PROCEDURE [dbo].[Transaction]
    (

    @Patient nvarchar(50),
    @E_TO nvarchar(50),
    @R_type int,
    @User_id uniqueidentifier,
    @ReportType nvarchar(50),
    @Patient_no int,
    @Patient_ID_NO numeric(10,0),
    @idcount numeric(18,0)=NULL output
    )
    AS
    BEGIN

    declare @tempid numeric(18,0)
    set @tempid = 0;
    declare @idcnt numeric(18,0)
    select @idcnt =isnull( max(idcount),0) from Transactions where year(R_date)=year(getdate())
    if (@idcnt =0)
    set @tempid=1
    else
    set @tempid = @idcnt +1
    INSERT INTO dbo.Transactions
    (
    Patient,E_TO,R_date,R_from,[User_id],
    report_type,Patient_no,Patient_ID_NO,idcount
    )
    values
    (
    @Patient,@E_TO,getdate(),@R_type,@User_id,@ReportType,@Patient_no,@Patient_ID_NO,@tempid
    )
    End

    Reply
  • Any Suggestions for how I could do this grouping by a header ID. Example: i have 1 invoice with 5 services, I want 1 row with 2 columns, Invoice and list of services. Here is how i am currently pulling this off using a subquery on the table i am selecting from, but i want to make it perform better:

    Stuff(
    (select ‘, ‘ + ServiceTypeWithNotes
    From WisCTE c
    where c.WorkItemID = cte.WorkItemID
    FOR XML Path(”)
    ),1,1,”) as ServiceList

    Reply
  • SELECT *FROM A
    SELECT *FROM C

    SELECT *FROM A
    WHERE NO1 NOT IN
    (
    SELECT NO1 FROM C
    )

    CREATE FUNCTION ITSV2(@A VARCHAR(10))
    RETURNS VARCHAR(MAX)
    AS
    BEGIN
    DECLARE @B VARCHAR(10)
    SET @B=”
    SELECT @B=NO1+’,’+@B FROM A
    WHERE NO1 NOT IN
    (SELECT
    NO1
    FROM
    C
    )
    RETURN SUBSTRING(@B

    END

    SELECT dbo.ITSV2(”)

    Reply
    • That only returns one column. I am looking for 2, first column being the header rowID the second being list of services for header rowID.

      I have a table with

      Header rowID Service
      1. A
      1. B
      1. C

      I’m looking for
      1. A,B,C

      With out doing a sub query joing on itself

      Select
      RowID
      ,(
      Select stuff(CommaDelimintedserviceList)
      From table1 b
      Where b.rowID = a.rowID
      )
      From table1 as a

      Or is a best way?

      Reply
  • Md. Shohel Rana
    August 14, 2012 10:00 am

    NICE EX.

    Reply
  • This is awesome along with everything else from Pinal Dave.

    Thanks

    Ned

    Reply
  • hi pinal;
    i want to store the select clause values into string.if i use one column able to store into string,but more than one column not able to store,please give any suggestion.

    Reply
  • I want to pass my result of in my select query ?? like this
    DECLARE @MaxTRxID as int
    DECLARE @Str as varchar(MAX)
    DECLARE @Str1 varchar(MAX)
    SET @MaxTRxID = 1700
    SELECT @Str = COALESCE(@Str + ‘,’, ”) + ReferenceNo FROM [iC.SI.WC.PosLog.TrxInfo]
    WHERE TrxId > @MaxTRxID
    SET @Str1 = @Str

    select * from [iC.SI.WC.PosLog.CashPayment.LineItem] where ReferenceNo in (@Str1)
    but its not working can anyone help?

    Reply
  • pLEASE SOMEONE HELP ME

    Reply
  • Hello Guys,

    I would appreciate if anyone could help me.
    I am using COALESCE exactly like in the initial example of the post (copied below)

    DECLARE @listStr VARCHAR(MAX)
    SELECT @listStr = COALESCE(@listStr+’,’ ,”) + Name
    FROM Production.Product
    SELECT @listStr

    My list is also Varchar(max) but i want to retrieve these values in ascending format using order by?
    Can i do this? and if yes could you please give me a simple example using the above script?
    Also if i cannot do this do you have any other idea how to achieve the same results?

    Thanks in advance!!

    Reply
    • This resolved with table variable in a function

      DECLARE @TableVar table (list VARCHAR(max) NOT NULL)

      INSERT INTO @TableVar ( list )
      SELECT Name
      FROM Production.Product
      ORDER BY Name

      DECLARE @listStr VARCHAR(MAX)
      SELECT @listStr = COALESCE(@listStr + ‘ ; ‘ , ”) + list
      from @TableVar

      RETURN @listStr

      Reply
  • it is possible to insert this code to function, which I able to call in the application code

    Reply
  • Now I have somone likie this:

    create procedure LIST_p(@COLUMN varchar (max), @TABLE varchar (max) )
    AS
    DECLARE @SYNTAX VARCHAR(MAX)

    SET @SYNTAX = ‘DECLARE @listStr VARCHAR(MAX) select @listStr = COALESCE(@listStr+”,” ,””) + ‘ + @COLUMN + ‘ from ‘+ @TABLE +’; select @listStr;’
    execute (@SYNTAX)
    go

    , but I have a question how to execute this procedure using select query

    Reply
  • Hi Pinal,

    How to create comma separated list of values by group.

    Reply
  • Hi this is satish bandi

    I want only in singe row from two rows Like

    Sno Name Age
    ”””””|”””””””’|””””’
    1 sa 20
    1 sa 30
    1 sa 40

    Now I want

    Sno Name Age
    ”””””|”””””””’|””””’
    1 sa 20,30,40

    Reply
  • Hi This IS satish

    id name services(I have)
    ———————————–
    1 Joe AA
    1 Joe AB
    1 Joe AC
    2 Judy GH
    2 Judy GC
    3 Kevin AA
    3 Kevin GH

    Result Set:

    id name services (I want)
    ———————————–
    1 Joe AA, AB, AC
    2 Judy GH, GC
    3 Kevin AA, GH

    How can i get like this

    Reply
  • solution to vyshu’s problem

    declare @tt varchar(25) = ”
    ,@ttlen tinyint = 0

    select @tt = ‘ab,bc,cd,de’
    ,@ttlen = LEN(@tt)

    create table #tt(name varchar(10))
    while(@ttlen > 0)
    begin
    insert into #tt
    select LEFT(ltrim(@tt),2)
    select @tt = STUFF(@tt,1,3,”)

    set @ttlen = LEN(ltrim(@tt))
    end
    select * from #tt

    Reply
  • same output i wanted using user defined function , i wanted to pass the ids with comma separed to the function and get the description as per the id.

    ex: my passing dynamic parameters ids should be like this ids = ‘1,4,5,6’ and from function it should return with comma separated description..like san,ta,sri,ssss.

    Thanks
    Santosh

    Reply

Leave a Reply