若
A am/pm
则
a am
a pm
____________________
若
a am/pm
b am/pm
则
a am
b pm
____________________
若
a am/pm/n
b am/pm/n
----------------
则
a am
b pm
n
A am/pm
则
a am
a pm
____________________
若
a am/pm
b am/pm
则
a am
b pm
____________________
若
a am/pm/n
b am/pm/n
----------------
则
a am
b pm
n
如果把
A am/pm
则
a am
a pm
改为
A am/pm
则
a am
pm
--就好做了!
则
a am
pm这样做也可以,如何做?
create table #tmp(sID int,iname varchar(10), times varchar(10), t tinyint)
insert into #tmp(sID,iname, times,t) values(1,'a','am/pm',2)
insert into #tmp(sID,iname, times,t) values(1,'b','am/pm',2)
insert into #tmp(sID,iname, times,t) values(1,'c','am/pm',2)
insert into #tmp(sID,iname, times,t) values(2,'d','am/pm',2)
insert into #tmp(sID,iname, times,t) values(3,'e','am/pm/n',3)
insert into #tmp(sID,iname, times,t) values(3,'f','am/pm/n',3)
insert into #tmp(sID,iname, times,t) values(4,'g','am/n',2)select * from #tmp
--要select 成为 如下表格式:
create table #des(sID int, iname varchar(10),times varchar(10))
insert into #des(sID,iname, times) values(1,'a','am')
insert into #des(sID,iname, times) values(1,'b','')
insert into #des(sID,iname, times) values(1,'c','pm')
insert into #des(sID,iname, times) values(2,'d','am')
insert into #des(sID,iname, times) values(2,'e','pm')
insert into #des(sID,iname, times) values(3,'e','am')
insert into #des(sID,iname, times) values(3,'f','pm')
insert into #des(sID,iname, times) values(3,'','n')
insert into #des(sID,iname, times) values(4,'g','am')
insert into #des(sID,iname, times) values(4,'','n')select * from #des
drop table #des
drop table #tmp/*
解释:times 但最多5个!例中,每个相同的 sid为一组(i行) , times 按照'/'分开,形成n行, 如果n>i,则在同一组插入 n-i的新行 , 新行的 iname为空,times 改为按'/' 分开后的每段.(am或pm) (增加新一列存放拆分后的times 值亦可).如果n<=i,则不插新行,times 改为按'/' 分开后的每段.(am或pm, am和pm不一定要放在一组的头和尾,只要拆分后放在这一组中即可,最后是头尾. 若 n<i 组中其余各行的 times 为空 )