SQL 把表中字段存储的逗号隔开内容转换成列表形式

浏览数：21 / 时间：2015年06月12日

　　我们日常开发中，不管是表设计问题抑或是其他什么原因，或多或少都会遇到一张表中有一个字段存储的内容是用逗号隔开的列表。

　　具体效果如下图：

　　技术分享 ------》

从左边图转换成右边图，像这种需求，我们难免会遇到。

今天我写了个存储过程来解决这种问题。主要方式是利用master..spt_values表。

具体存储过程如下：

-- Author:        LHM
-- Create date: 2015-01-10
-- Description:    把表中某一个列按照逗号拼接列表
--示例： EXEC [Sp_StringsToTable] ‘AgentId‘,‘UserId‘,‘Bse_GeneralAgent‘,‘‘
-- =============================================
CREATE   PROCEDURE [dbo].[Sp_StringsToTable]
    @ColumnId VARCHAR(100) ,
    @ColumnName VARCHAR(2047) ,
    @TableName NVARCHAR(100) ,
    @Filter VARCHAR(1000)=‘‘
AS
    BEGIN
        DECLARE @sql VARCHAR(500)
        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

这个存储过程有一个限制：就是@ColumnName的值不能超过2047个字节，也就是说，图中的UserId的字段里面的内容不能超过2047个字符。

原因就是因为master..spt_values表的限制。大家可以在数据库中执行 SELECT * FROM master..spt_values type=‘p‘ 就可以知道限制的原因了。

有兴趣的朋友可以试着建立如图的表

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

随意添加一些测试数据进行测试。只需执行存储过程

EXEC [Sp_StringsToTable] ‘AgentId‘,‘UserId‘,‘Bse_GeneralAgent‘,‘‘

希望给遇到此类需求的朋友带来帮助，谨此记录。

如果觉得有用，可以推荐一下，谢谢。

------------------------------------------------------------以下是指尖流淌的思路，感谢---------------------------------------------------

-- Author:        LHM
-- Create date: 2015-01-10
-- Description:    把表中某一个列按照逗号拼接列表
--示例： EXEC Sp_StringsToTableExtend ‘AgentId‘,‘UserId‘,‘Bse_GeneralAgent‘ 
-- =============================================
CREATE   PROCEDURE [dbo].Sp_StringsToTableExtend
    @ColumnId VARCHAR(MAX) ,
    @ColumnName VARCHAR(MAX) ,
    @TableName NVARCHAR(100)
AS
    BEGIN
        DECLARE @sql VARCHAR(500) 
        SET @Sql=‘SELECT  A.‘+@ColumnId+‘ ,  B.StrColumn
FROM    (SELECT StrXml = CONVERT(XML, ‘‘<root><v>‘‘+REPLACE(‘+@ColumnName+‘, ‘‘,‘‘, ‘‘</v><v>‘‘)+‘‘</v></root>‘‘) ,     ‘+@ColumnId+‘  ,    UserId    FROM   ‘
            +@TableName+‘   ) A OUTER APPLY (SELECT StrColumn = N.v.value(‘‘.‘‘, ‘‘nvarchar(40)‘‘)  FROM   A.StrXml.nodes(‘‘/root/v‘‘) N (v)   ) B ‘
        EXEC  (@Sql)
    END 
GO