表格如下:id time st1 st2
01 9:01 1 0
02 9:02 0 1
02 9:03 0 1
01 9:04 1 0
... .. .. ..st1/st2只有0/1,记录数目不等。只有两个ID。希望得到如下结果:
01-02-01-02
01 9:01 1 0
02 9:02 0 1
02 9:03 0 1
01 9:04 1 0
... .. .. ..st1/st2只有0/1,记录数目不等。只有两个ID。希望得到如下结果:
01-02-01-02
调试欢乐多
st1-st2-st2-st1
9:01 1 0
9:02 0 1
9:03 0 1
9:04 1 0
... .. .. .. st1/st2只有0/1,记录数目不等。希望得到如下结果:
st1-st2-st2-st1
declare @t table
(
id nvarchar(10),
time nvarchar(10),
st1 bit,
st2 bit
)insert @t
select '01', '9:01', 1, 0
union all
select '02', '9:02', 0, 1
union all
select '02', '9:03', 0, 1
union all
select '01', '9:04', 1, 0 declare @sql nvarchar(1000)
set @sql=''
select @sql=@sql+s+'-'
from
(
select time,'st1' as s
from @t
where st1=1
union all
select time,'st2'
from @t
where st2=1
) as tmp
order by timeselect left(@sql,len(@sql)-1)
declare @t table(id int, st1 int, st2 int)
insert into @t
select 01, 1, 0
union all select 02, 0, 1
union all select 02, 0, 1
union all select 01, 1, 0 declare @str nvarchar(50)
set @str=''
select @str = @str + case when st1=0 then 'st1' else (case when st1=1 then 'st2' else '' end) end +'->' from @t
select substring(@str,1,len(@str)-2)
/*
--------------------------------------------------
st2->st1->st1->st2*/
declare @t table(id int, st1 int, st2 int)
insert into @t
select 01 , 1 , 0
union all select 02 , 0 , 1
union all select 02 , 0 , 1
union all select 01 , 1 , 0 declare @str nvarchar(50)
set @str=''
select @str = @str + case when st1=0 then 'st2' else (case when st1=1 then 'st1' else '' end) end +'->' from @t
select substring(@str,1,len(@str)-2)
/*
--------------------------------------------------
st1->st2->st2->st1
*/
create table ByTime(
id varchar(50),
[time] datetime,
st1 int,
st2 int
)--测试数据
insert into ByTime
select '01','9:01',1,0
union all
select '02','9:02',0,1
union all
select '02','9:03',0,1
union all
select '01','9:04',1,0--测试
declare @d varchar(500)
set @d=''
select
@d=@d+'-'+(
case when st1='1' then '01' else '02' end)
from ByTime
print stuff(@d,1,1,'')--删除表
drop table bytime