sql中有如下数据
A B C D
a b 2 12/56
c d f 11/22
d dfdf gg 123456/88
......
如何变为:
A B C D
a b 2 12
a b 2 56
c d f 11
c d f 22
d dfdf gg 123456
d dfdf gg 88
......
就是将D字段中的数据以/为分隔符分为两行显示,请告诉赐教,谢谢!
A B C D
a b 2 12/56
c d f 11/22
d dfdf gg 123456/88
......
如何变为:
A B C D
a b 2 12
a b 2 56
c d f 11
c d f 22
d dfdf gg 123456
d dfdf gg 88
......
就是将D字段中的数据以/为分隔符分为两行显示,请告诉赐教,谢谢!
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U')) --U 代表你查询的是表
DROP TABLE [TB]
GO---->建表
create table [TB]([A] varchar(1),[B] varchar(4),[C] varchar(2),[D] varchar(9))
insert [TB]
select 'a','b','2','12/56' union all
select 'c','d','f','11/22' union all
select 'd','dfdf','gg','123456/88'
GO--> 查询结果
SELECT a.[A],a.[B],a.[C],b.col
FROM (SELECT [A],[B],[C],col=CAST('<v>'+REPLACE([D],'/','</v><v>')+'</v>' AS xml) FROM [TB]) a
OUTER APPLY (SELECT col=T.C.value('.','varchar(50)') FROM a.col.nodes('/v') AS T(C)) b
--> 删除表格
--DROP TABLE [TB]
-- 2005create table tb(ar varchar(10),ak varchar(50))
insert into tb
select 'a','d' union all
select 'b','12/56' union all
select 'c','123/1234/4233'
goselect distinct a.ar,substring(a.ak,b.number,charindex('/',a.ak+'/',b.number) - b.number)ak
from tb a,spt_values b
where 1 = 1 and b.number between 1 and len(a.ak)
and substring('/'+a.ak,b.number,1) = '/'drop table tbar ak
---------- --------------------------------------------------
a d
b 12
b 56
c 123
c 1234
c 4233(6 行受影响)
union all
select a , b, c , substring(d , charindex('/',d)+1 ,len(d)) d from tb where charindex('/',d) > 0
create table [TB]([A] varchar(1),[B] varchar(4),[C] varchar(2),[D] varchar(9))
insert [TB]
select 'a','b','2','12/56' union all
select 'c','d','f','11/22' union all
select 'd','dfdf','gg','123456/88'
GOselect distinct a.a,a.b,a.c,substring(a.d,b.number,charindex('/',a.d+'/',b.number) - b.number)d
from tb a,spt_values b
where 1 = 1 and b.number between 1 and len(a.d)
and substring('/'+a.d,b.number,1) = '/'drop table tb
a b c d
---- ---- ---- ---------
a b 2 12
a b 2 56
c d f 11
c d f 22
d dfdf gg 123456
d dfdf gg 88(6 行受影响)
select * from (
select A,B,C,left(D,charindex('/',D)-1) as D from tb
union all
select A,B,C,right(D,charindex('/',D)) as D from tb) aa
order by A,B,C,D
insert [TB]
select 'a','b','2','12/56' union all
select 'c','d','f','11/22' union all
select 'd','dfdf','gg','123456/88'
GOselect a , b, c , left(d , charindex('/',d)-1) d from tb where charindex('/',d) > 0
union all
select a , b, c , substring(d , charindex('/',d)+1 ,len(d)) d from tb where charindex('/',d) > 0
order by a , b, cdrop table tb/*
a b c d
---- ---- ---- ---------
a b 2 12
a b 2 56
c d f 22
c d f 11
d dfdf gg 123456
d dfdf gg 88(所影响的行数为 6 行)
*/
create table [TB]([A] varchar(1),[B] varchar(4),[C] varchar(2),[D] varchar(9))
insert [TB]
select 'a','b','2','12/56' union all
select 'c','d','f','11/22' union all
select 'd','dfdf','gg','123456/88'select * from (
select A,B,C,left(D,charindex('/',D)-1) as D from tb
union all
select A,B,C,right(D,len(D)-charindex('/',D)) as D from tb) aa
order by A,B,C,D/*
A B C D
---- ---- ---- ---------
a b 2 12
a b 2 56
c d f 11
c d f 22
d dfdf gg 123456
d dfdf gg 88