How can I "merge", "flatten" or "pivot" results from a query which returns multiple rows into a single result?

Question

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: I know I can use a cursor, but I would really prefer not to unless there is no better way.

The database is Sybase version 12.5.4.

Answer 1

Here is a solution:

SELECT DISTINCT
        id, 
        concatenation = LEFT(id_types, LEN(id_types) - 1) + ':' + LEFT(id_refs, LEN(id_refs) - 1) 
FROM (
SELECT  id, 
        id_types = (SELECT CAST(b.id_type AS nvarchar) + ',' FROM Table1 b WHERE b.id = a.id FOR XML PATH('')), 
        id_refs = (SELECT CAST(c.id_ref AS nvarchar) + ',' FROM Table1 c WHERE c.id = a.id FOR XML PATH('')) 
FROM    Table1 a
) t

UPDATE: Another approach

;WITH r(id, rnk, id_type, id_ref) AS 
(
    SELECT  id, 
            rnk = ROW_NUMBER() OVER(ORDER BY id),
            id_type = CAST(id_type AS nvarchar(MAX)), 
            id_ref = CAST(id_ref AS nvarchar(MAX)) 
    FROM Table1
), anchor(id, rnk, id_type, id_ref) AS 
(
    SELECT  id, 
            rnk, 
            id_type, 
            id_ref 
    FROM r
    WHERE rnk = 1
), result(id, rnk, id_type, id_ref) AS 
(
    SELECT  id, 
            rnk, 
            id_type, 
            id_ref 
    FROM anchor
    UNION ALL 
    SELECT  r.id, 
            r.rnk, 
            result.id_type + ',' + r.id_type, 
            result.id_ref + ',' + r.id_ref 
    FROM r
    INNER JOIN result ON r.id = result.id AND r.rnk = result.rnk + 1 
)
SELECT id, concatenation = MAX(id_type) + ':' +  MAX(id_ref)
FROM result
GROUP BY id

Answer 2

The best I could think now is the next one:

select  a.id id,
        str (a.id_type,4,0)||
        ','||str (b.id_type,4,0)||
        ','||str (c.id_type,4,0)||
        ','||str (d.id_type,4,0)||
        ','||str (e.id_type,4,0)||':'||
        str (a.id_ref,4,0)||
        ','||str (b.id_ref,4,0)||
        ','||str (c.id_ref,4,0)||
        ','||str (d.id_ref,4,0)||
        ','||str (e.id_ref,4,0) concatenation
  from  dbo.merge_test a,
        dbo.merge_test b,
        dbo.merge_test c,
        dbo.merge_test d,
        dbo.merge_test e
where a.id = b.id
and a.id = b.id
and a.id = c.id
and a.id = d.id
and a.id = e.id
and a.id_type < b.id_type
and b.id_type <c.id_type
and c.id_type < d.id_type
and d.id_type < e.id_type

But the result is a bit different than the one you typed...!!!

Answer 3

Another approach that works on Sybase ASE 12.5.4. The table must have a clustered index on id, in order for this to work. Assuming that table name is MYTABLE:

declare @strNew varchar(10), @strOld varchar(10), @str1 varchar(1000), @str2 varchar(1000)
set @str1 = NULL, @str2 = NULL, @strNew = NULL, @strOld = NULL

UPDATE MYTABLE
SET @strNew = convert(varchar,id) 
, @str1 = case when @strNew = @strOld then @str1 + convert(varchar,id_type) + "," else @str1 +  '$' + @strNew + '$' + convert(varchar,id_type) + "," end  
, @str2 = case when @strNew = @strOld then @str2 + convert(varchar,id_ref) + "," else @str2 + '$' + @strNew + '$' + convert(varchar,id_ref) + "," end
, @strOld = convert(varchar,id) 


select id, substring(@str1,charindex("$" + convert(varchar,id) + "$",@str1) + len("$" + convert(varchar,id) + "$"),
case when
    charindex(",$",substring(@str1,charindex("$" + convert(varchar,id) + "$",@str1) + len("$" + convert(varchar,id) + "$") + 1,len(@str1)))
    = 0 then len(@str1) - (charindex("$" + convert(varchar,id) + "$",@str1) + len("$" + convert(varchar,id) + "$"))
else
    charindex(",$",substring(@str1,charindex("$" + convert(varchar,id) + "$",@str1) + len("$" + convert(varchar,id) + "$") + 1,len(@str1)))
end
) 
+ ':' + 
substring(@str2,charindex("$" + convert(varchar,id) + "$",@str2) + len("$" + convert(varchar,id) + "$"),
case when 
    charindex(",$",substring(@str2,charindex("$" + convert(varchar,id) + "$",@str2) + len("$" + convert(varchar,id) + "$") + 1,len(@str2)))
    = 0 then len(@str2) - (charindex("$" + convert(varchar,id) + "$",@str2) + len("$" + convert(varchar,id) + "$"))
else
    charindex(",$",substring(@str2,charindex("$" + convert(varchar,id) + "$",@str2) + len("$" + convert(varchar,id) + "$") + 1,len(@str2)))
end
) as concatenation
from MYTABLE 
group by id

Answer 4

I don't have a sybase server to test, but reading the docs online, it appears that common table expressions are supported. I was unsure about ROW_NUMBER, as used in other solutions, so here is a solution that does not use that.

I believe sybase uses || for string concatenation, although the docs I read mentions that '+' can also be used, so I've used that. Please change as appropriate.

I've commented the query to try to explain what is going on.

The query concatenates all id_type and id_ref values with the same id, in increasing 'id_type' order.

/* a common table expression is used to concatenate the values, one by one */
WITH ConcatYourTable([id],  /* the id of rows being concatenated */
      concat_id_type,       /* concatenated id_type so far */
      concat_id_ref,        /* concatenated id_ref so far */
      last_id_type,         /* the last id_type added */
      remain)               /* how many more values are there to concatenate? */
AS 
(
  /* start with the lowest id_type value for some id */
  SELECT id, id_type, id_ref, 
     id_type, /* id_type was concatentated (it's presently the only value) */
     (SELECT COUNT(*) FROM YourTable f2 WHERE f2.id=f.id)-1
     /* how many more values to concatenate -1 because we've added one already */
  FROM YourTable f 
  WHERE NOT EXISTS
  /* start with the lowest value - ensure there are no other values lower. */
     (SELECT 1 FROM YourTable f2 WHERE f2.id=f.id AND f2.id_type<f.id_type)
  UNION ALL
  /* concatenate higher values of id_type for the same id */
  SELECT f.id, 
    c.id_type + ',' + f.id_type,   /* add the new id_type value to the current list */
    c.id_ref + ',' + f.id_ref,     /* add the new id_ref value to the current list */
    f.id_type,  /* the last value added - ensured subsequent added values are greater */
    c.remain-1  /* one less value to add */
  FROM ConcatYourTable c           /* take what we have concatenated so far */    
   INNER JOIN YourTable f  /* add another row with the same id, and > id_type */
     ON f.id = c.id AND f.id_type > c.last_id_type
     /* we really want the next highest id_type, not just one that is greater */
   WHERE NOT EXISTS (SELECT 1 FROM YourTable f2
     WHERE f2.id=f.id AND f2.id_type<f.id_type AND
     f2.id_type>c.last_id_type)
)
/* Select the rows where all values for and id were concatenated (remain=0) */
/* Concatenate the cumulated id_type and id_ref fields to format id_type values:id_ref values*/
SELECT id, id_type+':'+id_ref FROM ConcatYourTable 
WHERE remain=0

The query is quite "brutish" in that it doesn't use more sophisticated features that might improve readability or possibly performance. I've done this since I don't know sybase well, and used those features that I'm reasonably confident are supported. For best performance ensure id and (id,id_type) are indexed.

To use this in a trigger, such as an INSERT or UPDATE trigger to maintain a table based on this concatentate query, extend the WHERE clause of the base case (before UNION ALL) to include id=@changed_id. This will ensure only the concatenated row for the changed id is computed. You can then do what you want with the computed concatenated row. If you are materializing the concatenated query to a table, then DELETE the current concatenate row for @changed_id in the table, and INSERT a new row from the result of the concatenate query above. You could also check if your concatenate table already contains a value with the changed_id, and use an UPDATE statement instead.

Answer 5

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

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

Answer 6

Ok, forgive me if I'm missing something crucial here because I don't know the first thing about Sybase. But in mysql, this is absurdly simple so I figured it couldn't be as bad as the answers so far. So pulling from documentation that may or may not be relevant:

SELECT id, LIST(id_type) + ":" + LIST(id_ref) AS concatentation

Please inform me if I've misread something and I'll delete this.