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 (http://blog.SQLAuthority.com)

About these ads

37 thoughts on “SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP

  1. 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………………

    Like

  2. 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

    Like

  3. Pingback: SQL SERVER - Create a Comma Delimited List Using SELECT Clause From Table Column Journey to SQL Authority with Pinal Dave

  4. 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

    Like

  5. 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

    Like

  6. Pingback: SQL SERVER - Creating comma separate list from table Journey to SQL Authority with Pinal Dave

  7. I prefer such:

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

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

    Like

  8. 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()

    Like

  9. 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.

    Like

  10. 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

    Like

  11. 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

    Like

  12. 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!

    Like

  13. 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

    Like

  14. 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?

    Like

  15. Why are you separating rows into columns? A csv should have every row as a new row and columns separated by commas. You just hvae one row with each data row separated by commas. Horrible example.

    Like

  16. if it is integer/decimal field your query is returning accumulated value rather than comma separated value, could you please tell me the fix for integer/decimal value

    Like

    • Thanks, i managed to do

      DECLARE @listStr VARCHAR(MAX)
      SET @listStr = ”
      SELECT @listStr = CAST(Paidamount AS VARCHAR) + ‘, ‘ + @listStr
      FROM StudentDetails INNER JOIN
      Payment ON StudentDetails.Id = Payment.StudentId INNER JOIN
      PaymentCenter ON Payment.PaymentCenter = PaymentCenter.Id
      WHERE StudentDetails.Id = Payment.StudentId
      AND Payment.IsPaid 1 AND Payment.PaidYear = 2013 AND
      (StudentDetails.PaymentCenter = 1)
      SELECT @listStr

      Like

  17. Pingback: SQL SERVER – Weekly Series – Memory Lane – #028 | SQL Server Journey with SQL Authority

  18. I have a doubt regarding this. Is there anything special with ‘COALESCE’ here.?
    Cant we produce the same Output with ‘ISNULL’ using the below code.

    —Script—
    SELECT @ListStr = ISNULL(@ListStr + ‘,’,’Colours : ‘) + Colour
    FROM
    (
    SELECT ‘Red’ AS Colour
    UNION ALL
    SELECT ‘White’
    UNION ALL
    SELECT ‘Blue’
    UNION ALL
    SELECT ‘Black’
    )M

    –// Output
    SELECT @ListStr
    ————————————————–

    So why should we go for COALESCE.

    Thanks in advance.. :)

    Like

  19. select *, STUFF(
    (SELECT ‘, ‘ + cast(UGL.GROUPID as varchar(10))
    FROM USERINFO UI
    where UI.ID=UGL.USERID and COMPANY=’etis’
    FOR XML PATH (”)) , 1, 1, ”) AS [USER_GroupS]
    from USERGROUPLIST UGL

    Like

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