我如何“合并"，“展平"或“枢轴"查询的结果，该查询将多行返回到单个结果中? [英] How can I "merge", "flatten" or "pivot" results from a query which returns multiple rows into a single result?

问题描述

我对一个表进行了简单的查询，该查询返回的结果如下:

I have a simple query over a table, which returns results like the following:

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

例如，我想将结果合并为一行:

And I would like to merge the results into a single row, for instance:

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

在触发器中有什么方法可以做到吗?

Is there any way to do this within a trigger?

NB:我知道我可以使用游标，但我真的不愿意，除非没有更好的方法.

NB: I know I can use a cursor, but I would really prefer not to unless there is no better way.

该数据库是Sybase 12.5.4版.

The database is Sybase version 12.5.4.

推荐答案

由于使用select语句很难在Sybase中完成此操作，因此建议使用如下所示的while循环.虽然循环比游标更可取，但它的速度要快得多.假设表名是MYTABLE:

Since it's rather difficult to get this done in Sybase using a select statement I would suggest a while loop like the following. While loops are preferred over cursors for being much faster. Assuming that table name is MYTABLE:

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, concatenation) 
             VALUES (@idOld, left(@str1,len(@str1) - 1) + ':' + left(@str2,len(@str2) - 1) )
             set @str1 = NULL, @str2 = NULL
             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 (@idNew, left(@str1,len(@str1) - 1) + ':' + left(@str2,len(@str2) - 1) )
          END

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

select * from #results

编辑 以下版本的速度要快约45％

EDIT The following version is about 45% faster

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), @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) + ',' 

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

          END
        ELSE
          BEGIN
             insert into #results (id, concatenation) 
             VALUES (@idOld, left(str_replace(@str1, ',:', ':'),len(@str1) - 2) )
             set @str1 = ':'
             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) + ','

             IF @aa = @maxaa  
             insert into #results (id, concatenation) 
             VALUES (@idNew, left(str_replace(@str1, ',:', ':'),len(@str1) - 2) )
          END

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

select * from #results

这篇关于我如何“合并"，“展平"或“枢轴"查询的结果，该查询将多行返回到单个结果中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！