Data table column values converted to comma separated strings
Keywords:
SQL Server
SQL
Stored Procedure
xml
In developing SQL Server word order, it may be necessary to convert all values of a column in the table to comma separated strings for presentation.
for instance:
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
BEGIN
DROP TABLE #tempTable
END
CREATE TABLE #tempTable ([ID] INT NOT NULL,[Category] NVARCHAR(40) NULL)
INSERT INTO #tempTable ([ID],[Category]) VALUES (1,'Table'),
(2,'View'),
(3,'Store Procedure'),
(4,'Table-valued Function'),
(5,'Scalar-valued Function'),
(6,'User-Defined Table Type')
SELECT [ID],[Category] FROM #tempTable
GO
Source Code
The result is required, any column of [ID] or [Category], as shown below:
In the future, we don't know which table and which field it is.
Therefore, it can be written as a dynamic SQL stored procedure to handle:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Insus.NET
-- Create date: 2019-05-11
-- Update date: 2019-05-11
-- Description: Convert column values to comma separated strings
-- =============================================
CREATE PROCEDURE [dbo].[usp_TableColumnValueToCommaDelimitedString] (
@tableName SYSNAME,
@columnName SYSNAME,
@ReturnValue NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
DECLARE @sql NVARCHAR(MAX) = N'
DECLARE @temporary_table AS TABLE([multirow_comma-delimited_string] NVARCHAR(MAX))
INSERT INTO @temporary_table ([multirow_comma-delimited_string])
SELECT TOP(1) STUFF(
REPLACE(
RTRIM(
(SELECT ''|'' + CAST('+ @columnName +' AS NVARCHAR(MAX)) FROM '+ @tableName +' FOR XML PATH('''') )
),
''|'','', ''),
1,1,'''') FROM '+ @tableName +'
SELECT @ReturnValue = [multirow_comma-delimited_string] FROM @temporary_table'
EXECUTE sp_executesql @sql,
N'@ReturnValue NVARCHAR(MAX) OUTPUT',
@ReturnValue OUTPUT
END
Source Code
Just pass in the table name, field name and other parameters for the above stored procedure to get the result we want:
