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.
Reference : Pinal Dave (https://blog.sqlauthority.com)
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?
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
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?
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.
Huh, special chars were removed from my previous comment :-)
Dave, you’re the man!
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
Hi Manish,
You can generate list of comma separated file as described here at:
Thanks,
Tejas
SQLYoga.com
@manish
you can use distinct before column name
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?
I tried to shove the above code into a SQL 2012 CHOOSE(2, STUFF()) function the return is NULL any suggestions?
how to write a store procedure for comma seperated values in sybase ASE
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]
Is there an alternative in the later versions of SQL server?