一行变多行（整理总结）

--XML
DECLARE @one Table(    
    CompanyID INT,    
    CompanyCodes VARCHAR(100) 
) 
 
insert into @one 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 @one 
) 
SELECT 
    CompanyID, 
    x.i.value('.', 'VARCHAR(10)') AS CompanyCode 
FROM cte 
CROSS APPLY CompanyCodes.nodes('i') x(i) 

--案例二
declare @two table(autoid int,partid varchar(50),location varchar(100));
insert into @two(autoid,partid,location)
select 14,'HAMP-025-0002','C190 C234 C237' union all
select 15,'HAMP-025-0038','D27 D28 D29';


select a.autoId,a.partId,b.vx
from 
(select autoid,partid,cast('<root><v>'+REPLACE(location,' ','</v><v>')+'</v></root>' as xml) as x from @two) a
outer apply(
    select vx=N.v.value('.','varchar(100)') from a.x.nodes('/root/v') N(v)
) b

--案例 三
declare @three table
(id int,name char(1),yh varchar(20))

insert into @three
select 1, 'a', '0001,0002,0003' union all
select 2, 'b', '0002,0003' union all
select 3, 'c', '0001,0004'


select a.name,
substring(','+a.yh,b.number+1,charindex(',',a.yh+',',b.number)-b.number) 'yh'
from @three a
inner join master.dbo.spt_values b
on b.[type]='P' and 
substring(','+a.yh,b.number,1)=','