1

Is there any easy way you can simulate GROUP_CONCAT functionality in Ingres 9.2?

I have a table which has something like :

OrderID   LineNumber    LineText
1         1             This is an example note which is trunc
1         2             ated at a certain point.
2         1             Another note which is just one line.

And so on. Some notes are 1 line, others are 50+ lines.

I want a query to return:

OrderID  FullText
1        This is an example note which truncated at a certain point.
2        Another note which is just one line.

In MySQL or SQLite I'd use GROUP_CONCAT. In MS SQL it's more difficult but I'd use the FOR XML functionality to achieve a solution. I'm not sure how I could do this in Ingres. I started writing a stored procedure which could return the concatenated notes for a single order id, but I couldn't see an easy way of integrating that into my queries.

Any ideas?

dantefs
  • 174
  • 6

1 Answers1

2

This might work:

select OrderId,
       (max(case when LineNumber = 1 then LineText else '' end) +
        max(case when LineNumber = 2 then LineText else '' end) +
        max(case when LineNumber = 3 then LineText else '' end)
       ) as LineText
from t
group by Orderid;

It is very convenient that you have LineNumber.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks, I'll try this and report back. My only concern is that I'm not sure if there's any upper limit on how many lines a note could have. If it's 999 then the code may end up being a bit verbose. – dantefs May 28 '13 at 21:01
  • @dantefs . . . I agree with you. However, the alternative would be using a cursor, and that might be even less appealing. – Gordon Linoff May 28 '13 at 21:12
  • This was the solution I went with. Some notes are 2000 lines long which obviously this won't work for but it copes with the vast majority of our entries. Thanks again. – dantefs May 30 '13 at 09:35