sql – 如何从一个将多行返回到单个结果的查询中“合并”,“展平”或“旋转”结果？

我对表有一个简单的查询,它返回如下结果：

id    id_type  id_ref
2702  5        31
2702  16       14
2702  17       3
2702  40       1
2703  23       4
2703  23       5
2703  34       6
2704  1        14

我想将结果合并为一行,例如：

id    concatenation 
2702  5,16,17,40:31,14,3,1
2703  23,23,34:4,5,6
2704  1:14

在触发器中有没有办法做到这一点？

注意：我知道我可以使用光标,但我真的不愿意,除非没有更好的方法.

该数据库是Sybase 12.5.4版.

解决方法

CREATE TABLE #temp
(                               
aa            numeric(5,0)  identity,id            int           not null,id_type       int           not null,id_ref        int           not null
)

CREATE TABLE #results
(                                                        
id            int           not null,concatenation varchar(1000) not null,)

insert into #temp
select id,id_type,id_ref from MYTABLE order by id

declare @aa int,@maxaa int,@idOld int,@idNew int
declare @str1 varchar(1000),@str2 varchar(1000)

set @aa = 1
set @maxaa = (select max(aa) from #temp) 
set @idNew = (select id from #temp where aa = 1),@idOld = @idNew

while @aa <= @maxaa 
    begin
        set @idNew = (select id from #temp where aa = @aa) 
        IF @idNew = @idOld
          BEGIN
             set @str1 = @str1 + convert(varchar,(select id_type from #temp where aa = @aa)) + ',',@str2 = @str2 + convert(varchar,(select id_ref from #temp where aa = @aa)) + ','

             IF @aa = @maxaa  
             insert into #results (id,concatenation) 
             VALUES (@idOld,left(@str1,len(@str1) - 1) + ':' + left(@str2,len(@str2) - 1) )

          END
        ELSE
          BEGIN
             insert into #results (id,len(@str2) - 1) )
             set @str1 = NULL,@str2 = NULL
             set @str1 = @str1 + convert(varchar,' 

             IF @aa = @maxaa  
             insert into #results (id,concatenation) 
             VALUES (@idNew,len(@str2) - 1) )
          END

        set @idOld = @idNew 
        set @aa = @aa+1
    end

select * from #results

编辑
以下版本的速度提高了约45％

CREATE TABLE #temp
(                               
aa            numeric(5,id_ref from MYTABLE order by id
declare @aa int,@str2 varchar(1000),@j int

set @aa = 1
set @maxaa = (select max(aa) from #temp) 
set @idNew = (select id from #temp where aa = 1),@idOld = @idNew
set @str1 = ':'

while @aa <= @maxaa 
    begin
        set @idNew = (select id from #temp where aa = @aa) 
        IF @idNew = @idOld
          BEGIN
             set @str2 = (select convert(varchar,id_type) + ':' + convert(varchar,id_ref) from #temp where aa = @aa)
             set @j = (select charindex(':',@str2))
             set @str1 = str_replace(@str1,':',substring(@str2,1,@j - 1) + ',:') + right(@str2,len(@str2) - @j) + ',left(str_replace(@str1,:',':'),len(@str1) - 2) )

          END
        ELSE
          BEGIN
             insert into #results (id,len(@str1) - 2) )
             set @str1 = ':'
             set @str2 = (select convert(varchar,len(@str1) - 2) )
          END

        set @idOld = @idNew 
        set @aa = @aa+1
    end

select * from #results

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!