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

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

  具体效果如下图:

  ------》

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

今天我写了个存储过程来解决这种问题。主要方式是利用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‘,‘‘

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

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

时间: 01-10

[原创]SQL 把表中字段存储的逗号隔开内容转换成列表形式的相关文章

[原创]SQL 把表中某一个列按照逗号拼接成一行

在我们开发的过程中,难免遇到一种场景,把某个表中的的某个列的值拼接成用逗号隔开的一行数据 如图:我们把UserId列拼接成一行数据 -------> 为此我写了一个存储过程来解决此类问题. -- Author:        LHM -- Create date: 2015-01-15 -- Description:    把表中某一个列按照逗号拼接成一行 --示例: EXEC [Sp_TableToString] 'cast([UserId] as varchar)','[X_RoleUser

在一个SQL Server表中的多个列找出最大值

在一个SQL Server表中一行的多个列找出最大值 有时候我们需要从多个相同的列里(这些列的数据类型相同)找出最大的那个值,并显示 这里给出一个例子 IF (OBJECT_ID('tempdb..##TestTable') IS NOT NULL) DROP TABLE ##TestTable CREATE TABLE ##TestTable ( ID INT IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(40), UpdateByApp1Date DATE

导出 SQL SERVER 表中数据为脚本

ALTER PROCEDURE [dbo].[Usp_OutputData] @tablename sysname, @outputIdentitycolumn int AS declare @column varchar(3000) declare @columndata varchar(8000) declare @sql varchar(8000) declare @xtype tinyint declare @name sysname declare @objectId int decl

关于数据库优化1——关于count(1),count(*),和count(列名)的区别,和关于表中字段顺序的问题

1.关于count(1),count(*),和count(列名)的区别 相信大家总是在工作中,或者是学习中对于count()的到底怎么用更快.一直有很大的疑问,有的人说count(*)更快,也有的人说count(列名)更快,那到底是谁更快,我将会在本文中详细介绍一下到底是count(1),count(*)和count(列明)的区别,和更适合的使用场景. 往常在工作中有人会说count(1)比count(*)会快,或者相反,首先这个结论肯定是错的,实际上count(1)和count(*)并没有区别

修改MySQL数据库中表和表中字段的编码方式的方法

今天向MySQL数据库中的一张表添加含有中文的数据,可是老是出异常,检查程序并没有发现错误,无奈呀,后来重新检查这张表发现表的编码方式为latin1并且原想可以插入中文的字段的编码方式也是latin1,然后再次仔细观察控制台输出的异常,进一步确定是表和表中字段编码不当造成的,那就修改表和其中对应的字段呗,网上找了一会儿,你别说还真有,执行完sql脚本后果然可以存入中文了,尽管如此还是认为有必要总结一下,古人云:好记性不如烂笔头嘛,呵呵呵. 修改表的编码方式:ALTER TABLE `test`

如何查出同一张表中字段值重复的记录

如何查出同一张表中字段值重复的记录 (2013-03-25 11:28:51) 转载▼ 比如现在有一人员表(表名:peosons) 若想将姓名.身份证号.住址这三个字段完全相同的记录查询出来select p1.* from persons p1,persons p2where p1.idp2.id and p1.cardid = p2.cardid and p1.pname = p2.pname and p1.address = p2.address 可以实现上述效果. 几个删除重复记录的SQL

不允许修改SQLserver2008r2表中字段的属性问题

SQLserver2008r2修改表中字段的属性时弹出 点击工具->选项,取消阻止保存要求重新创建表的更改

使用SQL如何把用逗号等字符隔开的字符串转换成列表(转)

如何把用逗号等字符隔开的字符串转换成列表,下面依逗号分隔符为例: 比如有一个字符串,其值为:香港,张家港,北京,上海用SQL把这个字符串转换成列表的方法是: 1.方法一 WITH A AS (SELECT '香港,张家港,北京,上海' A FROM DUAL) SELECT DECODE(B,0,SUBSTR(A,C),SUBSTR(A,C,B-C)) city FROM ( SELECT A,B,(LAG(B,1,0) OVER(ORDER BY LV))+1 C FROM( SELECT A

将传入结构体 pMtInfo 中包含的数据内容转换成 JSON 字符串返回

upu_struct.h封装了有关  pMtInfo结构体的内容,用到的部分如下图所示: 利用jansson库实现将传入结构体 pMtInfo 中包含的数据内容转换成 JSON 字符串返回 代码如下: #include <stdio.h> #include <string.h> #include "jansson.h" #include "upu_struct.h" #include "upu_proto_parse.h"