日期:2014-05-18 浏览次数:20536 次
create table wanglejun(电话号码 varchar(50)) insert into wanglejun select '13478908796,12345678,11111' -- 拆分为行 select '电话号码'+cast(row_number() over(order by getdate()) as varchar) '电话号码', substring(a.电话号码,b.number,charindex(',',a.电话号码+',',b.number)-b.number) 'n' from wanglejun a inner join master.dbo.spt_values b on b.[type]='P' and substring(','+a.电话号码,b.number,1)=',' 电话号码 n ---------------- ----------------- 电话号码1 13478908796 电话号码2 12345678 电话号码3 11111 (3 row(s) affected) -- 拆分为列 with t as (select '电话号码'+cast(row_number() over(order by getdate()) as varchar) '电话号码', substring(a.电话号码,b.number,charindex(',',a.电话号码+',',b.number)-b.number) 'n' from wanglejun a inner join master.dbo.spt_values b on b.[type]='P' and substring(','+a.电话号码,b.number,1)=',') select [电话号码1], [电话号码2], [电话号码3] from t pivot(max(n) for 电话号码 in ([电话号码1], [电话号码2], [电话号码3])) t 电话号码1 电话号码2 电话号码3 ------------ ---------- ----------- 13478908796 12345678 11111 (1 row(s) affected)