• SQL 逗号分隔将一行拆成多行


    有表tb, 如下:

    id          value

    ----------- -----------

    1           aa,bb

    2           aaa,bbb,ccc

    欲按id,分拆value列, 分拆后结果如下:

    id          value

    ----------- --------

    1           aa

    1           bb

    2           aaa

    2           bbb

    2           ccc

    */

    --1. 旧的解决方法(sql server 2000)

    create table tb(id int,value varchar(30))

    insert into tb values(1,'aa,bb')

    insert into tb values(2,'aaa,bbb,ccc')

    go

    --方法1.使用临时表完成

    SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO FROM syscolumns a, syscolumns b 

    SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id)

    FROM tb A, # B

    WHERE SUBSTRING(',' + A.[value], B.id, 1) = ','

    DROP TABLE #

    --方法2.如果数据量小,可不使用临时表

    select a.id , value = substring(a.value , b.number , charindex(',' , a.value + ',' , b.number) - b.number) 

    from tb a join master..spt_values  b 

    on b.type='p' and b.number between and len(a.value)

    where substring(',' + a.value , b.number , 1) = ','

    --2. 新的解决方法(sql server 2005)

    create table tb(id int,value varchar(30))

    insert into tb values(1,'aa,bb')

    insert into tb values(2,'aaa,bbb,ccc')

    go

    --方法1.使用xml完成

    SELECT A.id, B.value FROM

    (

      SELECT id, [value] = CONVERT(xml,'<root><v>' REPLACE([value], ',''</v><v>') + '</v></root>'FROM tb

    ) A OUTER APPLY

    (

      SELECT value = N.v.value('.''varchar(100)'FROM A.[value].nodes('/root/v') N(v)

    ) B

    --方法2.使用CTE完成

    ;with tt as 

    (select id,[value]=cast(left([value],charindex(',',[value]+',')-1) as nvarchar(100)),Split=cast(stuff([value]+',',1,charindex(',',[value]+','),''as nvarchar(100)) from tb

    union all

    select id,[value]=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),''as nvarchar(100)) from tt where split>''

    )

    select id,[value] from tt order by id option (MAXRECURSION 0)

    DROP TABLE tb

    /*

    id          value

    ----------- ------------------------------

    1           aa

    1           bb

    2           aaa

    2           bbb

    2           ccc

  • 相关阅读:
    C#中的global::system***命名空间别名限定符
    返回一个整数数组中最大子数组的和
    敏捷开发概述
    单词查找排序输出
    关于电梯调度的设计
    关于电梯调度的一些想法
    C#中抽象类和接口的区别
    SharePoint2010列表表单:用后台代码生成表单
    外刊IT评论:远离.net
    程序员:编程给你现实生活带来了哪些坏习惯
  • 原文地址:https://www.cnblogs.com/chenqingbin/p/12923543.html
Copyright © 2020-2023  润新知