Original SQL converts the comma-separated contents of a field stored in a table into a list form

In our daily development, whether it is a table design problem or any other reason, more or less you will encounter a table with a field stored in a comma-separated list of content.

Specific effects such as:

------》

From the diagram on the left to the right, like this demand, we will inevitably encounter.

Today I wrote a stored procedure to solve this problem. The main way is to use master. Spt_values table.

The specific stored procedures are as follows:

--AUTHOR:LHM--Create date:2015-01-10--Description: List A column in a table by commas--Example: EXEC [sp_stringstotable] ' agentid ', ' UserId ', ' bse_generalagent ', '-- =============================================CREATE   PROCEDURE [dbo].[sp_stringstotable]    @ColumnId VARCHAR( -) ,    @ColumnName VARCHAR(2047) ,    @TableName NVARCHAR( -) ,    @Filter VARCHAR( +)="' as    BEGIN        DECLARE @sql VARCHAR( -)        IF(@Filter<>"')            BEGIN                 SET @Sql='Select'+@ColumnId+', RTRIM (LTRIM (substring ('+@ColumnName+'+"',"', A.number,charindex ("',"','+@ColumnName+'+"',"', a.number+1)-a.number))) Id from master: Spt_values A,'+@TableName+'b where'+@Filter+'and a.type="'P"'and substring ("',"'+'+@ColumnName+', a.number,1) ="',"' '                    END        ELSE            BEGIN                SET @Sql='Select'+@ColumnId+', RTRIM (LTRIM (substring ('+@ColumnName+'+"',"', A.number,charindex ("',"','+@ColumnName+'+"',"', a.number+1)-a.number))) Id from master: Spt_values A,'+@TableName+'b where a.type="'P"'and substring ("',"'+'+@ColumnName+', a.number,1) ="',"' '            END            EXEC(@Sql)    END 

There is a limit to this stored procedure: The value of @columnname cannot exceed 2047 bytes, meaning that the contents of the UserID in the figure cannot exceed 2047 characters.

The reason is because master. Limitations of the Spt_values table. You can execute SELECT * from Master in the database. Spt_values type= ' P ' can know the reason for the limitation.

Interested friends can try to build the table

CREATE TABLE [dbo].[bse_generalagent](    [Agentid] [int] IDENTITY(1,1) not NULL,    [UserId] [varchar](Max)NULL, CONSTRAINT [pk_bse_generalagent] PRIMARY KEY CLUSTERED (    [Agentid] ASC) with(Pad_index= OFF, Statistics_norecompute= OFF, Ignore_dup_key= OFF, Allow_row_locks=  on, Allow_page_locks=  on) on [PRIMARY])  on [PRIMARY]GO

Feel free to add some test data for testing. Simply execute the stored procedure

EXEC [sp_stringstotable] ' agentid ', ' UserId ', ' bse_generalagent ', '

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

If you feel useful, you can recommend it, thank you.

Original SQL converts the comma-separated contents of a field stored in a table into a list form