日期:2014-05-17 浏览次数:20653 次
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 行受影响)
*/
create table u01
(序号 int,
字段1 varchar(10))
insert into u01
select 1, 'a,b,c' union all
select 2, 'a,e' union all
select 3, 'd'
-- 诉求1:将字段1转为行
select substring(a.字段1,b.number,charindex(',',a.字段1+',',b.number)-b.number) '字段1'
from u01 a
inner join master..spt_values b
on b.[type]='P' and b.number between 1 and len(a.字段1)
and substring(','+a.字段1,b.number,1) = ','
/*
字段1
----------
a
b
c
a
e
d
(6 row(s) affected)
*/
-- 诉求2:统计字段1不同字母的数量
with t as
(
select substring(a.字段1,b.number,charindex(',',a.字段1+',',b.number)-b.number) '字段1'
from u01 a
inner join master..spt_values b
on b.[type]='P' and b.number between 1 and len(a.字段1)
and substring(','+a.字段1,b.number,1) = ','
)
select count(distinct 字段1) '数量' from t
/*
数量
-----------
5
(1 row(s) affected)
*/