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

[mychinabc]

表数据：
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.

[Felixzhaowenzhong]

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)

刚刚看完的一个案例。

[Felixzhaowenzhong]

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)

[问苍天人生几何]

3楼正解!

[AcHerat]

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 行受影响)

[Felixzhaowenzhong]

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

[Monkey__D__Luffy]

凑个热闹

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

[-晴天]

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,#

[q806294478]

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

[CalvinR]

好多大牛 我的数据库要加强了

[黄_瓜]

/****************************************************************************************************************************************************** 
合并分拆表数据 

整理人：中国风(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
*/

[yanyuchonglou]

这应该不是问题吧。

[喜阳阳]

[Quote=引用 5 楼 acherat 的回复:]
SQL code


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……
[/Quote]up

[jmx123456789]

[Quote=引用 10 楼 maco_wang 的回复:]
SQL code


--分拆列值
--原著：邹建
--改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳

/*
有表tb, 如下:
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
*/
--欲按id,分拆……
[/Quote]

我也看过邹老大的博客.

[jmx123456789]

。。。不知道是这个表情 随便蒙了个数字。。这表情没别的意思

[勤king]

受教了[Quote=引用 10 楼 maco_wang 的回复:]
SQL code

--分拆列值
--原著：邹建
--改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳

/*
有表tb, 如下:
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
*/
--欲按id,分拆va……
[/Quote]

[wzx5137]

sql真是无所不能啊。

[叶子]

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

[mychinabc]

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

[(¯(oo)¯) (¯(oo)¯)]

Good !

[(¯(oo)¯) (¯(oo)¯)]

Good