SQL SERVER – Comma Separated Values (CSV) from Table Column – Part 2

In my earlier post, I wrote about how one can use XML to convert table to string SQL SERVER – Comma Separated Values (CSV) from Table Column. The same article is also published on channel 9 SQLAuthority News – Featured on Channel 9. One of the very interesting points that was discussed on show was about the usage of function SUBSTRING. I found the following point very valid: SUBSTRING usage limits the length of the XML to be used.

I have re-written the same function with function STUFF, and it removes any limit imposed on the script.

USE AdventureWorks
GO
-- Check Table Column
SELECT [Name]
FROM HumanResources.Shift
GO
-- Get CSV values
SELECT STUFF(
(SELECT ',' + s.Name
FROM HumanResources.Shift s
ORDER BY s.Name
FOR XML PATH('')),1,1,'') AS CSV
GO

Do let me know your thoughts on the same.

SQL SERVER - Comma Separated Values (CSV) from Table Column - Part 2 stuffsub

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

, , ,
Previous Post
SQLAuthority News – Migrating DTS Packages to Integration Services
Next Post
SQLAuthority News – Meeting SQL Expert Imran at Hyderabad

Related Posts

14 Comments. Leave new

  • This function is an attempt to reproduce the functionality of the build-in group_concat from MySQL, why is it that this is still missing from SQL Server?

    Reply
    • Yes. MySQL has a Group_concat function to do the same task
      But usually this kind of thing should be done in front end application

      Reply
  • Hi Pinal,
    Please check this way, and let me know is it okay or it has some issues?

    GO

    — We can write a function for it

    CREATE FUNCTION [dbo].[GetShifts] ()
    RETURNS NVARCHAR(MAX)
    WITH EXECUTE AS CALLER
    AS
    BEGIN
    declare @output varchar(max)
    set @output = null;
    select @output = COALESCE(@output + ‘, ‘, ”) + convert(varchar(max),[Name])
    from [MyRnDDataBase].[dbo].[HumanResources.Shift]

    RETURN @output

    END

    — Check Table Column
    SELECT [Name]
    FROM [MyRnDDataBase].[dbo].[HumanResources.Shift]

    — AND Now we’ll just call that function
    SELECT [dbo].GetShifts() AS CSV

    It’ll give you the same result plus it provides code reusability as we just need to call the function wherever necessary. Isn’t it?

    Reply
  • Pinal, you still have to look out for some special characters when performing a concatenation with FOR XML PATH clause. For example, if you would like to concatenate three values: aaa, bbb and you would get < and > instead of chars. I would rather recommend writing your own CLR aggregate function for this kind of operation.

    Reply
  • Huh, special chars were removed from my previous comment :-)

    Reply
  • Dave, you’re the man!

    Reply
  • Is there a way to remove duplicates?

    for e.g. Table has two columns

    Col A Col B
    Test1 Val1
    Test2 Val2
    Test1 Val3

    if i have select stuff on colA and stuff on colB the result is something like
    Col A ColB
    Test1,Test2, Val1,Val2,Val3
    Test1

    how would i remove duplicate from ColA and display only Test1,Test2

    Reply
  • @manish

    you can use distinct before column name

    Reply
  • Chintan Gandhi
    February 2, 2012 2:53 pm

    I agree, we cannot use this if the strings contain special characters like ampersand,less than , etc

    Is there another solution apart from using COLAESECE?

    Reply
  • I tried to shove the above code into a SQL 2012 CHOOSE(2, STUFF()) function the return is NULL any suggestions?

    Reply
  • how to write a store procedure for comma seperated values in sybase ASE

    Reply
  • SET NOCOUNT ON
    IF EXISTS(SELECT TOP 1 1 FROM SYS.objects WHERE NAME LIKE ‘DUPLICATE’)
    DROP TABLE DUPLICATE

    CREATE TABLE DUPLICATE (ID INT IDENTITY(1,1) NOT NULL,NAME VARCHAR(100))
    GO
    INSERT INTO DUPLICATE (NAME) VALUES (‘SELVEESWARAN’)
    GO 100

    DECLARE
    @TABLENAME SYSNAME
    , @OBJECTID INT

    DECLARE [TABLES] CURSOR READ_ONLY FAST_FORWARD LOCAL FOR
    SELECT
    ‘[‘ + S.NAME + ‘].[‘ + T.NAME + ‘]’
    , T.[OBJECT_ID]
    FROM (
    SELECT DISTINCT
    T.[SCHEMA_ID]
    , T.[OBJECT_ID]
    , T.NAME
    FROM SYS.OBJECTS T WITH (NOWAIT)
    JOIN SYS.PARTITIONS P WITH (NOWAIT) ON P.[OBJECT_ID] = T.[OBJECT_ID]
    WHERE P.[ROWS] > 0
    AND T.[TYPE] = ‘U’
    ) T
    JOIN SYS.SCHEMAS S WITH (NOWAIT) ON T.[SCHEMA_ID] = S.[SCHEMA_ID]
    WHERE T.NAME IN (‘DUPLICATE’)

    OPEN [TABLES]

    FETCH NEXT FROM [TABLES] INTO
    @TABLENAME
    , @OBJECTID

    DECLARE
    @SQLINSERT NVARCHAR(MAX)
    , @SQLCOLUMNS NVARCHAR(MAX)
    , @SQLTINYCOLUMNS NVARCHAR(MAX)
    ,@SQLCOLS NVARCHAR(MAX)

    WHILE @@FETCH_STATUS = 0
    BEGIN

    SELECT
    @SQLINSERT = ”
    , @SQLCOLUMNS = ”
    , @SQLTINYCOLUMNS = ”

    ;WITH cols AS
    (
    SELECT
    C.NAME
    , DATETYPE = T.NAME
    , C.COLUMN_ID
    FROM SYS.COLUMNS C WITH (NOWAIT)
    JOIN SYS.TYPES T WITH (NOWAIT) ON C.SYSTEM_TYPE_ID = T.SYSTEM_TYPE_ID AND C.USER_TYPE_ID = T.USER_TYPE_ID
    WHERE C.[OBJECT_ID] = @OBJECTID
    AND C.IS_COMPUTED = 0
    AND T.NAME NOT IN (‘XML’, ‘GEOGRAPHY’, ‘GEOMETRY’, ‘HIERARCHYID’)
    )
    SELECT
    @SQLCOLS = STUFF((
    SELECT ‘;’ + C.NAME
    FROM COLS C
    ORDER BY C.COLUMN_ID
    FOR XML PATH, TYPE, ROOT).value(‘.’, ‘NVARCHAR(MAX)’), 1, 1, ”),

    @SQLTINYCOLUMNS = STUFF((
    SELECT ‘, [‘ + C.NAME + ‘]’
    FROM COLS C
    ORDER BY C.COLUMN_ID
    FOR XML PATH, TYPE, ROOT).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, ”)

    , @SQLCOLUMNS = STUFF((SELECT CHAR(13) +
    CASE
    WHEN C.DATETYPE = ‘UNIQUEIDENTIFIER’
    THEN ‘ + ”;” + ISNULL(”” + CAST([‘ + C.NAME + ‘] AS VARCHAR(MAX)) + ””, ”NULL”)’
    WHEN C.DATETYPE IN (‘NVARCHAR’, ‘VARCHAR’, ‘NCHAR’, ‘CHAR’, ‘VARBINARY’, ‘BINARY’)
    THEN ‘ + ”;” + ISNULL(”” + CAST(REPLACE([‘ + C.NAME + ‘], ””, ””””) AS NVARCHAR(MAX)) + ””, ”NULL”)’
    WHEN C.DATETYPE = ‘DATETIME’
    THEN ‘ + ”;” + ISNULL(”” + CONVERT(VARCHAR, [‘ + C.NAME + ‘], 120) + ””, ”NULL”)’
    ELSE
    ‘ + ”;” + ISNULL(CAST([‘ + C.NAME + ‘] AS NVARCHAR(MAX)), ”NULL”)’
    END

    FROM COLS C
    ORDER BY C.COLUMN_ID
    FOR XML PATH, TYPE, ROOT).value(‘.’, ‘NVARCHAR(MAX)’), 1, 10, ‘CHAR(13) + ”” +’)

    DECLARE @SQL NVARCHAR(MAX) = ‘
    SET NOCOUNT ON;
    DECLARE
    @SQL NVARCHAR(MAX) = ””
    , @X INT = 1
    , @COUNT INT = (SELECT COUNT(1) FROM ‘ + @TABLENAME + ‘)

    IF EXISTS(
    SELECT 1
    FROM TEMPDB.DBO.SYSOBJECTS
    WHERE ID = OBJECT_ID(”TEMPDB..#IMPORT”)
    )
    DROP TABLE #IMPORT;

    SELECT ‘ + @SQLTINYCOLUMNS + ‘, ”ROWNUMBER” = ROW_NUMBER() OVER (ORDER BY ‘ + @SQLTINYCOLUMNS + ‘)
    INTO #IMPORT
    FROM ‘ + @TABLENAME + ‘
    PRINT ”’+ @SQLCOLS +”’
    WHILE @X < @COUNT BEGIN

    SELECT @SQL = STUFF((
    SELECT ' + @SQLCOLUMNS + ' + ''''' + '
    FROM #IMPORT
    WHERE ROWNUMBER BETWEEN @X AND @X + 1
    FOR XML PATH, TYPE, ROOT).value(''.'', ''NVARCHAR(MAX)''), 1, 1, '''')

    PRINT(@SQL)

    SELECT @X = @X + 1

    END'

    EXEC SYS.SP_EXECUTESQL @SQL

    FETCH NEXT FROM [TABLES] INTO
    @TABLENAME
    , @OBJECTID

    END

    CLOSE [TABLES]
    DEALLOCATE [TABLES]

    Reply
  • Josetuttu Vazhaparambil
    July 3, 2019 6:03 pm

    Is there an alternative in the later versions of SQL server?

    Reply

Leave a Reply

Menu