表A:
name      num
aa       5/6/9
bb       6/7/8想要的结果:
col1      col2
5          aa
6          aa
6          bb
7          bb
8          bb
9          aa哪位知道应该如何查询

解决方案 »

  1.   

    --參考
    --拆分表:--> --> (Roy)生成測試數據
     
    if not object_id('Tab') is null
        drop table Tab
    Go
    Create table Tab([Col1] int,[COl2] nvarchar(5))
    Insert Tab
    select 1,N'a,b,c' union all
    select 2,N'd,e' union all
    select 3,N'f'
    Go--SQL2000用辅助表:
    if object_id('Tempdb..#Num') is not null
        drop table #Num
    go
    select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
    Select 
        a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) 
    from 
        Tab a,#Num b
    where
       substring(','+a.COl2,b.ID,1)=','
    --2000不适用辅助表
    Select
        a.Col1,COl2=substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number) 
    from 
        Tab a join master..spt_values  b 
        ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)
    where
         substring(','+a.COl2,b.number,1)=','
         --SQL2005用Xml:select 
        a.COl1,b.Col2
    from 
        (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
    outer apply
        (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
    --SQL05用CTE:;with roy as 
    (select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
    union all
    select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
    )
    select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)--生成结果:
    /*
    Col1        COl2
    ----------- -----
    1           a
    1           b
    1           c
    2           d
    2           e
    3           f
    */
      

  2.   

    分拆列值 原著:邹建 
    改编:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-16  广东深圳 有表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) 
    SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id) 
    FROM tb A, # B 
    WHERE SUBSTRING(',' + A.[values], B.id, 1) = ',' DROP TABLE # 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 
    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 DROP TABLE tb /* 
    id          value 
    ----------- ------------------------------ 
    1          aa 
    1          bb 
    2          aaa 
    2          bbb 
    2          ccc (5 行受影响) 
    */
      

  3.   


    create table test(name varchar(20),num varchar(20))
    insert test
    select 'aa','5/6/9' union all
    select 'bb','6/7/8'
    go--drop table testselect
        a.name[col2],col1=substring(a.num,b.number,charindex('/',a.num+'/',b.number)-b.number) 
    from 
        test a join master..spt_values  b 
        ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.num)
    where
         substring('/'+a.num,b.number,1)='/'
    order by col1     col2                 col1                 
    -------------------- -------------------- 
    aa                   5
    aa                   6
    bb                   6
    bb                   7
    bb                   8
    aa                   9(所影响的行数为 6 行)
      

  4.   

    ----------------------------------------------------------------
    -- Author  :fredrickhu(我是小F,向高手学习)
    -- Date    :2009-09-08 16:19:47
    -- Version:
    --      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
    -- Nov 24 2008 13:01:59 
    -- Copyright (c) 1988-2005 Microsoft Corporation
    -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
    --
    ----------------------------------------------------------------
    --> 测试数据:[tb]
    if object_id('[tb]') is not null drop table [tb]
    go 
    create table [tb]([name] varchar(2),[num] varchar(20))
    insert [tb]
    select 'aa','5/6/9' union all
    select 'bb','6/7/8'
    --------------开始查询--------------------------
    SELECT A.name, B.num 
    FROM( 
        SELECT name, [num] = CONVERT(xml,' <root> <v>' + REPLACE([num], '/', ' </v> <v>') + ' </v> </root>') FROM tb 
    )A 
    OUTER APPLY( 
        SELECT num = N.v.value('.', 'varchar(100)') FROM A.[num].nodes('/root/v') N(v) 
    )B ----------------结果----------------------------
    /* name num
    ---- ----------------------------------------------------------------------------------------------------
    aa   5 
    aa   6 
    aa   9 
    bb   6 
    bb   7 
    bb   8 (6 行受影响)*/