SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP

Following script will create common separate values (CSV) or common separate list from tables. convert list to table. Following script is written for SQL SERVER 2005. It will also work well with very big TEXT field. If you want to use this on SQL SERVER 2000 replace VARCHAR(MAX) with VARCHAR(8000) or any other varchar limit. It will work with INT as well as VARCHAR.

There are three ways to do this. 1) Using COALESCE 2) Using SELECT Smartly 3) Using CURSOR.

The table is example is:
TableName: NumberTable

NumberCols
first
second
third
fourth
fifth

Output : first,second,third,fourth,fifth

Option 1: This is the smartest way.
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' , '') + NumberCols
FROM NumberTable
SELECT @listStr

Please make a note that COALESCE returns the first NOT NULL value from the argument list we pass.

Option 2: This is the smart but not the best way; though I have seen similar code many times.
DECLARE @listStr VARCHAR(MAX)
SET @listStr = ''
SELECT @listStr = @listStr + NumberCols + ','
FROM NumberTable
SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1)

I sometime use ISNULL(NumberCols,’NullValue’) to convert NULL values to other desired value.

Option 3: Cursor are not the best way, please use either of above options.

Above script can be converted to User Defined Function (UDF) or Storped Procedure (SP).

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

,
Previous Post
SQL SERVER – UDF – Function to Convert List to Table
Next Post
SQL SERVER – 2005/2000 Examples and Explanation for GOTO

Related Posts

46 Comments. Leave new

  • Prashant Pandey
    September 17, 2007 1:45 am

    Hi Pinal,
    U have used COALESCE( colname + ‘,’,”)
    What is the meaning of Second parameter here.????

    Reply
  • i tried using both ways 1st and 2nd
    when i use these techniques on large set of data ,say one thousand records i get a timed out sql exception.this works fine on small amount of data (few rows).

    how could i achieve this on large data………………

    Reply
  • Hi Pinal,

    I have a column with dx codes separated by commas. I need to break these out into separate columns within the same table. How is this done or what is the best way to get this done?

    Here is an example:
    DX
    ————
    V72.0, V32.0, 245.

    OUTPUT NEEDED:

    DX DX1 DX2 DX3
    ——– ——- —— ——-
    V72.0, V32.0, 245. V72.0 V32.0 245

    Thanks!
    Mina

    Reply
  • Mahendra Patil
    June 23, 2008 11:44 am

    Hi,

    I used COALESCE in my project to reterive the comma separate values.

    syntax :-

    COALESCE (storage, separetor, startvalue)

    storage :- variable which contain final output. In option 1 @listStr is storage.

    separetor :- value which distigush between two item. for example here ‘,’ (comma) you can put any value like ‘/’,’A’.

    startvalue :- list start with this value.

    for example

    I want Output : start , first,second,third,fourth,fifth

    COALESCE(@listStr, ‘,’ , ‘start,’).

    thanks
    Mahendra

    Reply
  • Hi Pinal,

    In my company we need to do opposite of above and that is parsing the comma seperated values into rows of table.

    I found following script which works very fast as I have parsed 20 million records in just under 5 mins.

    Hope this will help others too.

    –First Create a table with numeric values form 1 to 8000
    CREATE TABLE Tally
    (ID INT)

    DECLARE @count INT

    SELECT @count =1

    WHILE @count < 8001
    BEGIN
    INSERT INTO Tally(ID)
    SELECT @count

    SELECT @count=@count+1
    END

    SELECT Comma_Seperated_List,
    NULLIF(SubString(‘,’ + product_list + ‘,’ , ID , CharIndex(‘,’ , ‘,’ + product_list + ‘,’ , ID) – ID) , ”) AS ‘Value’
    FROM Tally T,
    My_table M
    WHERE ID 0 –remove this line to keep NULL rows

    Your comments are appriciated.

    Thanks,
    SK

    Reply
  • Thanks you are the best…

    Reply
  • Is it possible to get distinct values only?

    Reply
  • How to get only distinct rows as comma separated?

    Reply
    • The code should be

      DECLARE @listStr VARCHAR(MAX)
      SELECT @listStr = COALESCE(@listStr+’,’ , ”) + NumberCols
      FROM (SELECT DISTINCT NumberCols from NumberTable) as t
      SELECT @listStr

      Reply
  • Tomek Cwajda
    July 9, 2009 8:46 pm

    I prefer such:

    Select name+’,’
    from something
    for xml path(”)

    The POWER :-) of this solution that you can use this in subquerys also.

    Reply
  • Tomek Cwajda
    July 9, 2009 8:55 pm

    Better I make some example:
    —————————————————————–
    SELECT campaign_name
    , ( SELECT member_email +’;’
    FROM competition (NOLOCK)
    WHERE campaign_key = C.c_key
    FOR XML PATH(”)
    ) recipients
    FROM campaigns (NOLOCK) AS C
    WHERE DATE > GETDATE()

    Reply
  • Shobhit Awasthi
    July 31, 2009 5:26 pm

    Hi Pinal,

    I want the record to be display

    ID Name

    1 abc, cde, efg
    2 111,222,333
    3 56

    Reply
  • Hi,

    please refer:

    Let me know if you have any other situations.

    Thanks,

    Tejas

    Reply
  • There is a better Option available for the same job.

    select cast(NumberCols as varchar)+’,’ from NumberTable
    for xml path (”)

    it saves so many operations and rest all query it self speaks.

    Reply
  • Hi Pinal,

    Thank you ,this article helped me a lot.

    Reply
  • Sreedhar Vankayala
    June 7, 2011 3:02 am

    You can try this also (Tested in SQL 2005)

    SELECT schema_Name(schema_id) AS SchemaName,
    name AS TABLE_NAME,
    ColumnsCount ,
    STUFF(COLUMN_NAME, 1, 1, ”) AS COLUMN_NAME
    FROM sys.tables t
    CROSS APPLY
    ( SELECT ‘,’ + name AS [text()]
    FROM sys.columns c
    WHERE c.object_id = t.object_id FOR XML PATH(”)
    ) o (COLUMN_NAME)
    CROSS APPLY
    ( SELECT COUNT(*) ColumnsCount
    FROM sys.columns c
    WHERE c.object_id = t.object_id
    ) CC
    — Where t.name not in (”)
    ORDER BY 1, 2, 3

    Reply
  • Table :

    ID NAME City
    1 ABC MCLEAN
    2 ABC RESTON
    3 BCD ASHBURN
    4 BCD HERNDON
    5 DEF CHICAGO
    ——————————————

    Declare @Name varchar(MAX)
    Select @Name = COALESCE(@Name+’,’,”)+ ”” + Name + ”” FROM (Select Distinct NAME from Testtable) as t
    Select @Name

    Declare @Front Varchar(5000)
    Select @Front = ‘(‘ + @Name + ‘)’
    Select @Front

    Select * from Testtable
    Where (NAME in (@Front))

    I want to see above query works, I want the @Front to work, as I have to use Variable to pass.

    Appreciate any reply back .

    Thanks
    Venkat

    Reply
  • This is awesome.

    Thank you!

    Reply
  • Hi Pinal,

    We can get comma separate values using single query.. see below example,

    SELECT
    STUFF
    (( SELECT ‘, ‘ + NumberCols
    FROM
    NumberTable
    WHERE
    NumberCols is not NULL
    ORDER BY
    NumberCols
    FOR XML
    PATH (”), TYPE ).value(‘.’, ‘varchar(max)’), 1, 2, ”) AS commaseperatelist

    What you say ? its good for best practice or ??… ?

    Thank You!

    Reply
  • Shovan Mukherjee
    June 28, 2012 11:38 am

    In case your table list is empty, you will get a error in the o/p
    If you use like below u wont get such error…
    DECLARE @listStr VARCHAR(MAX)
    SET @listStr = ”
    SELECT @listStr = @listStr + NumberCols + ‘,’
    FROM NumberTable
    if(@listStr ”)
    SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1)

    Print @listStr

    Reply
  • Enrique Fundora
    August 2, 2012 6:22 am

    I would like to use the resulting list as parameters to a stored procedure; will I have to parse the list to add quotation marks around the string values?

    Reply

Leave a Reply Cancel reply

Menu
Exit mobile version