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开发者_StackOverflow
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.
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
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
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.
Use the row level function.
your query:
select distinct id ,fn(id) from table1
function:
fn(@id int)
(
declare @res varchar
select @res = @res+id_ref+"," from table1 where id=@id
return @res
)
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
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...!!!
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.
精彩评论