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

Solarwinds
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)

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

Related Posts

44 Comments. Leave new

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

    Reply
  • I used the following query to obtain this result

    SELECT
    STUFF((SELECT ‘,’ + NumberCols FROM NumberTable
    FOR XML PATH(”)), 1, 2, ”)

    Reply
  • 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

    Reply
    • 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

      Reply
  • Sujay Chandran P.V
    November 15, 2013 6:05 pm

    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.. :)

    Reply
  • Thanks a lot dear….

    Reply
  • 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

    Reply
  • What is NumberCols ? How can we dynamically create the column list without knowing the number of columns in the table?

    Reply
  • how can i achieve same when i have output from 2 columns.

    so instead of NumberCols i have NumberCols1 + NumberCols2 as NumberCols

    Reply
  • In the first scenario, the result set will return ‘NULL’, if column NumberCols will have any NULL value.
    We can use the below query :

    declare @str varchar(max)
    select @str=coalesce(@str+’,’,”)+NumberCols from NumberTable
    where NumberCols is not null
    select @str

    Please correct me if I am wrong.

    Reply
  • Thanks for this!

    Reply
  • Bikesh srivastava
    August 5, 2016 3:34 pm

    // Create coloumn with seprated column value.from row value
    SELECT DISTINCT p.[Account ID],p.[Contact ID],p.[Account Name],p.[Email],p.[Contact Role],
    STUFF((SELECT distinct ‘,’ + p1.Role
    FROM finalsheet p1
    WHERE p.[Contact ID]= p1.[Contact ID]
    FOR XML PATH(”), TYPE
    ).value(‘.’, ‘NVARCHAR(MAX)’)
    ,1,1,”) RoleS
    FROM finalsheet p
    –group by p.[Account ID],p.[Contact ID],p.[Account Name],p.[Email],p.[Contact Role]

    Reply
  • // How about by using FOR XML PATH?
    declare @Result nvarchar(max)
    set @Result = (select [NumberCols] + ‘, ‘ from NumberTable FOR XML PATH(”))
    select substring(@Result,0,len(@Result)-1) as Result

    Reply
  • How do you create a coma delimited string from a row of multiple columns? meaning if I have [row] 1 | 2 | 3 | 4 and I want it to display 1,2,3,4 which I can set to a string variable.

    Reply
  • How to get each column value separated by comma and create multiple rows for new combination of the column value in SQL?

    I’m trying to split the value in comma separated column in SQL table and re-combine using below logic to create additional rows for the same id.

    for example: lets say I have a table call table_A

    id value
    1 a,b,c,d
    2 a,b,c
    3 a,b
    I know how to split these values into multiple rows with a single value but now i need them to be displayed like below.

    final output should be look like this:

    id value
    1 a,b
    1 b,c
    1 c,d
    2 a,b
    2 b,c
    3 a,b
    Any idea would be appreciated!

    Reply
  • CREATE TABLE Testdata
    (
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
    )

    INSERT Testdata SELECT 1, 9, ‘18,20,22’
    INSERT Testdata SELECT 2, 8, ‘17,19’
    INSERT Testdata SELECT 3, 7, ‘13,19,20’
    INSERT Testdata SELECT 4, 6, ”
    INSERT Testdata SELECT 9, 11, ‘1,2,3,4’

    ; with c as
    (
    SELECT A.SomeID,
    sa.a.value(‘.’, ‘VARCHAR(10)’) AS Data
    ,ro=row_number() over (partition by A.SomeID order by sa.a.value(‘.’, ‘VARCHAR(10)’))

    FROM
    (
    SELECT SomeID,
    CAST (” + REPLACE(String, ‘,’, ”) + ” AS XML) AS Data
    FROM Testdata
    ) AS A CROSS APPLY Data.nodes (‘M’) AS sa(a)
    )
    ,
    ccc as (
    select c.* ,strcon = stuff((select ‘,’ + c1.data
    from c c1
    where c.SomeID = c1.SomeID and c.ro <= (c1.ro) and (c1.ro – c.ro)<=1
    for xml path(''),type).value('.','nvarchar(max)'),1,1,'')
    ,max(ro) over (partition by SomeID ) max_ro
    from c
    )
    select SomeID , strcon
    from ccc
    where ( max_ro ro or max_ro =1)
    ;

    Reply

Leave a Reply

Menu