Original SQL to stitch a column of a table into a row by commas

In our development process, it is unavoidable to encounter a scene, the value of a column in a table is stitched into a comma-separated row of data

We're stitching up the UserID column into a row of data

-------》

So I wrote a stored procedure to solve this kind of problem.

--AUTHOR:LHM
--Create date:2015-01-15

--Description: A column in a table is stitched into a row by commas
--Example: EXEC [sp_tabletostring] ' cast ([UserId] as varchar) ', ' [X_roleuser] ', ' roleid=1 '

-- =============================================
CREATE PROCEDURE [dbo]. [Sp_tabletostring]
@ColumnName varchar (100),
@TableName nvarchar (100),
@Filter varchar (1000) = "
As
BEGIN
DECLARE @sql varchar (500)
IF (@Filter <> ")
BEGIN
SET @Sql = ' Select result= STUFF ((select ' ', ' + ' [email protected]+ ' from ' [email protected]+ ' WHERE ' [emai L protected]+ ' for XML PATH (') '), 1, 1, ') '
END
ELSE
BEGIN
SET @Sql = ' Select result= STUFF ((select ', ' + ') [email protected]+ ' from ' [email protected]+ ' for XML PATH (' ') '), 1, 1, ') '
END
EXEC (@Sql)
END

If you are interested in a friend can build a test table, provide a table statement

CREATE TABLE [dbo]. [X_roleuser] (
[ID] [int] IDENTITY (*) Not NULL,
[Roleid] [INT] Not NULL,
[UserId] [INT] Not NULL,
[COMPANY_ID] [INT] Null
CONSTRAINT [Pk_x_roleuser] PRIMARY KEY CLUSTERED
(
[Id] ASC
) with (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, Allow_page_locks = O N) on [PRIMARY]
) on [PRIMARY]

GO

After adding some test data, and then executing the stored procedure EXEC [sp_tabletostring] ' cast ([UserId] as varchar) ', ' [X_roleuser] ', ' roleid=1 ' can see the test effect.

One problem here is that if the field you want to convert in the table is not a string type, you should use the CAST function to convert it to a string.

I would like to make a note of this to help friends who meet such needs.

if friends think that may be used later, welcome reprint, of course, can also recommend, thank you.

PS: This blog post is written using Windows Live Writer 2012, and the format effect may not be good.

Original SQL to stitch a column of a table into a row by commas