--xml을 이용한 split
--drop table #temp
create table #temp(t char(1), val int)
insert #temp values('a', 1)
insert #temp values('a', 2)
insert #temp values('a', 3)
insert #temp values('b', 4)
insert #temp values('b', 5)
insert #temp values('b', 6)
insert #temp values('c', 7)
go
--xpath
select
t
,stuff((select ';' + cast(val as varchar(100)) as 'text()' from #temp where a.t = t for xml path('')),1,1,'') rs from (select distinct t from #temp) a
/* 결과
t rs
---- ----------------------------------------------------------------------------------------------------
a 1;2;3
b 4;5;6
c 7
*/
--xquery
declare
@str varchar(8000)
, @gb varchar(20)
, @xml xml;
set @str = '1;2;3;4'
set @gb = ';'
set @str = '<root><r>' + replace(@str, @gb, '</r><r>') + '</r></root>'
set @xml = cast(@str as xml)
select x.col.value('.', 'varchar(100)') as rs
from @xml.nodes('/root/r') x(col)
/* 결과
rs
----------------------------------------------------------------------------------------------------
1
2
3
4
*/ 출처: www.databaser.net |
|
|