【100分】请问SQL行数据中分别以""的数据单独转换成一行数据怎么转_Sql Server

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

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

------解决方案--------------------
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('' + REPLACE(CompanyCodes, '|', '') + '' AS XML) AS CompanyCodes
FROM @tb
)
SELECT
CompanyID,
x.i.value('.', 'VARCHAR(10)') AS CompanyCode
FROM cte
CROSS APPLY CompanyCodes.nodes('i') x(i)

刚刚看完的一个案例。
------解决方案--------------------
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('' + REPLACE(F2, ',', '') + '' 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楼正解!
------解决方案--------------------

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 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           11           22           12           22           33           13           23           33           44           14           24           34           44           5(14 行受影响)
------解决方案--------------------
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

------解决方案--------------------
凑个热闹
SQL codecreate 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))asbegin    set @[email protected]+','    while CHARINDEX(',',@str)>0       begin          insert into @t            select left(@str,CHARINDEX(',',@str)-1)           set @str=right(@str,len(@str)-CHARINDEX(',',@str))       end     return   endselect a.F1,b.* from tb across apply f(a.F2)b/*F1   F---- ----a    1a    2a    5b    1b    3c    3c    5