【100分】请教SQL行数据中分别以","的数据单独转换成一行数据如何转？

Question

表数据：
F1     F2
a      1,2,5
b      1,3
c      3,5要得到F1     F2
a       1
a       2
a       5
b       1
b       3
c    3
c       5
       
就是把后面的F2中每个值变一行数据出来请各位大侠多多指点,thanks. 

Answer 1

DECLARE @tb Table(    
    CompanyID INT,    
    CompanyCodes VARCHAR(100) 
) 
 
insert into @tb select 1,'1|2' 
union all select 2,'1|2|3' 
union all select 3,'1|2|3|4'
union all select 4,'1|2|3|4|5';WITH cte AS ( 
    SELECT 
        CompanyID, 
        CAST('<i>' + REPLACE(CompanyCodes, '|', '</i><i>') + '</i>' AS XML) AS CompanyCodes 
    FROM @tb 
) 
SELECT 
    CompanyID, 
    x.i.value('.', 'VARCHAR(10)') AS CompanyCode 
FROM cte 
CROSS APPLY CompanyCodes.nodes('i') x(i) 刚刚看完的一个案例。

Answer 2

DECLARE @tb Table(    
    F1 varchar(12),    
    F2 VARCHAR(100) 
) 
 
insert into @tb select 'a','1,2,5' 
union all select 'b','1,3' 
union all select 'c','3,5';WITH cte AS ( 
    SELECT 
        F1, 
        CAST('<i>' + REPLACE(F2, ',', '</i><i>') + '</i>' AS XML) AS F2 
    FROM @tb 
) 
SELECT 
    F1, 
    x.i.value('.', 'VARCHAR(10)') AS F2 
FROM cte 
CROSS APPLY F2.nodes('i') x(i) 

Answer 3

DECLARE @tb Table(   
  CompanyID INT,   
  CompanyCodes VARCHAR(100)  
)  
 
insert into @tb select 1,'1|2'  
union all select 2,'1|2|3'  
union all select 3,'1|2|3|4'
union all select 4,'1|2|3|4|5'select a.CompanyID,
CompanyCodes = substring(a.CompanyCodes,b.number,charindex('|',a.CompanyCodes+'|',b.number)-b.number)
from @tb a join master..spt_values b 
on b.[type] = 'p' and b.number between 0 and len(a.CompanyCodes)
and substring('|'+a.CompanyCodes,b.number,1) = '|'/****************CompanyID   CompanyCodes
----------- ----------------------------------------------------------------------------------------------------
1           1
1           2
2           1
2           2
2           3
3           1
3           2
3           3
3           4
4           1
4           2
4           3
4           4
4           5(14 行受影响)

Answer 4

DECLARE @tb Table(    
    F1 varchar(12),    
    F2 VARCHAR(100) 
) 
 
insert into @tb select 'a','1,2,5' 
union all select 'b','1,3' 
union all select 'c','3,5'select a.F1,b.vx
from 
(select F1,cast('<root><v>'+REPLACE(F2,',','</v><v>')+'</v></root>' as xml) as x from @tb) a
outer apply(
    select vx=N.v.value('.','varchar(100)') from a.x.nodes('/root/v') N(v)
) b

Answer 5

凑个热闹
create table tb(F1 char(1),F2 varchar(10))
insert into tb 
  select 'a','1,2,5' union 
  select 'b','1,3' union 
  select 'c','3,5'  
  
create function f(@str varchar(20))
returns @t table (F varchar(2))
as
begin
    set @str=@str+','
    while CHARINDEX(',',@str)>0
       begin
          insert into @t 
           select left(@str,CHARINDEX(',',@str)-1)
           set @str=right(@str,len(@str)-CHARINDEX(',',@str))
       end 
    return   
end
select a.F1,b.* from tb a
cross apply f(a.F2)b/*
F1   F
---- ----
a    1
a    2
a    5
b    1
b    3
c    3
c    5

Answer 6

2005:
create table tb(F1 varchar(10),F2 varchar(20))
insert into tb select 'a','1,2,5'
insert into tb select 'b','1,3'
insert into tb select 'c','3,5'
go
select a.F1,substring(a.F2,b.number,charindex(',',a.F2+',',b.number+1)-b.number)F2
from tb a,master..spt_values b
where b.type='p' and b.number<=len(a.F2) and substring(a.F2,b.number,1)<>',' and substring(','+a.F2,b.number,1)=','
/*
F1         F2
---------- --------------------
a          1
a          2
a          5
b          1
b          3
c          3
c          5(7 行受影响)*/
go
drop table tb
2000:
create table tb(F1 varchar(10),F2 varchar(20))
insert into tb select 'a','1,2,5'
insert into tb select 'b','1,3'
insert into tb select 'c','3,5'
go
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
SELECT A.F1, SUBSTRING(A.F2, B.id, CHARINDEX(',', A.F2 + ',', B.id) - B.id) F2
FROM tb A, # B
WHERE SUBSTRING(',' + A.F2, B.id, 1) = ','
/*
F1         F2
---------- --------------------
a          1
a          2
a          5
b          1
b          3
c          3
c          5(7 行受影响)*/
go
DROP TABLE tb,#

Answer 7

--分拆列值
--原著：邹建
--改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  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 行受影响) 
*/

Answer 8

CREATE Table #tb(   
  F1 varchar(12),   
  F2 VARCHAR(100)  
)  
 
insert into #tb select 'a','1,2,5'  
union all select 'b','1,3'  
union all select 'c','3,5'
--方法一：
SELECT  A.F1,B.F2
FROM(
  SELECT F1, F2 = CONVERT(xml,'<root><v>' + REPLACE(F2, ',', '</v><v>') + '</v></root>') FROM #tb
)A
OUTER APPLY(
  SELECT F2 = N.v.value('.', 'varchar(100)') FROM A.F2.nodes('/root/v') N(v)
)B
go
--方法二：
select a.F1,F2=SUBSTRING(a.F2,b.number,CHARINDEX(',',a.F2+',',b.number)-b.number)
from #tb a join master..spt_values b on b.type='p' and b.number <=len(a.F2)
where CHARINDEX(',',','+a.F2,b.number)=b.number
结果为：
F1 F2
a 1
a 2
a 5
b 1
b 3
c 3
c 5

Answer 9

/****************************************************************************************************************************************************** 
合并分拆表数据 整理人：中国风(Roy) 日期:2008.06.06 
******************************************************************************************************************************************************/ --> --> (Roy)生成測試數據 if not object_id('Tab') is null 
    drop table Tab 
Go 
Create table Tab([Col1] int,[Col2] nvarchar(1)) 
Insert Tab 
select 1,N'a' union all 
select 1,N'b' union all 
select 1,N'c' union all 
select 2,N'd' union all 
select 2,N'e' union all 
select 3,N'f' 
Go 合并表: SQL2000用函数: go 
if object_id('F_Str') is not null 
    drop function F_Str 
go 
create function F_Str(@Col1 int) 
returns nvarchar(100) 
as 
begin 
    declare @S nvarchar(100) 
    select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1 
    return @S 
end 
go 
Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab go SQL2005用XML: 方法1: select 
    a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'') 
from 
    (select distinct COl1 from Tab) a 
Cross apply 
    (select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b 方法2: select 
    a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44)) 
from 
    (select distinct COl1 from Tab) a 
cross apply 
    (select Col2=(select COl2 from Tab  where COl1=a.COl1 FOR XML AUTO, TYPE) 
                .query(' <Tab> 
                {for $i in /Tab[position() <last()]/@COl2 return concat(string($i),",")} 
                {concat("",string(/Tab[last()]/@COl2))} 
                </Tab>') 
                )b SQL05用CTE: ;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab) 
,Roy2 as 
(select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1 
union all 
select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1) 
select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0) 
生成结果: 
/* 
Col1        COl2 
----------- ------------ 
1          a,b,c 
2          d,e 
3          f (3 行受影响) 
*/ 
--参考
拆分表:--> --> (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
    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 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
*/

Answer 10

好多大牛，我的SQL要加强学习啊，结分!