表格如下: id time st1 st2
01 9:01 1 0
01 9:02 0 1
01 9:03 0 1
01 9:04 1 0 02 9:02 0 1
02 9:03 0 1
02 9:04 1 0 .. .... .. ...st1/st2只有0/1,记录数目不等。 希望得到如下结果:
01 st1-st2-st2-st1
02 st2-st2-st1
01 9:01 1 0
01 9:02 0 1
01 9:03 0 1
01 9:04 1 0 02 9:02 0 1
02 9:03 0 1
02 9:04 1 0 .. .... .. ...st1/st2只有0/1,记录数目不等。 希望得到如下结果:
01 st1-st2-st2-st1
02 st2-st2-st1
insert ta select
'01','9:01',1,0 union select
'01','9:02',0,1 union select
'01','9:03',0,1 union select
'01','9:04',1,0 union select
'02','9:02',0,1 union select
'02','9:03',0,1 union select
'02','9:04',1,0
gocreate function f_getstr(@id char(2))
returns varchar(100)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+'-'+case when st1 = 1 then 'st1' else case when st2 = 1 then 'st2' else '' end end from ta where id=@id
select @str=right(@str,len(@str)-1)
return @str
end
go
select id,dbo.f_getstr(id) as [output]
from ta
group by id
drop function f_getstr
drop table ta/*
id output
---- ----------------------------------------------------------------------------------------------------
01 st1-st2-st2-st1
02 st2-st2-st1(所影响的行数为 2 行)
*/
create function f_link(@id varchar(10))
returns varchar(100)
as
begin
declare @s varchar(100)
select @s=isnull(@s,'')+(case when st1=1 then 'st1-' when st2=1 then 'st2-' else '' end) from tb where id=@id order by time
set @s=substring(@s,1,len(@s)-1)
return @s
end
在调用这个SQL语句
select id,dbo.f_link(id) from tb group by id
returns varchar(100)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+'-'+case when st1 = 1 then 'st1' else case when st2 = 1 then 'st2' else '' end end from ta where id=@id order by [time]
select @str=right(@str,len(@str)-1)
return @str
end
go