Rows to list as columns

FranklinRaj22
FranklinRaj22 used Ask the Experts™
on
I have a table with Acct No , seq no and message 3 columns . example
Acct No   Seq No   Message
test01         1            A
test01          2           B
test02         1            C
test02          2            D
I wanna write a query whose out put shoudl be like , pelase advice
test01    1     2    AB
test02    1     2    CD
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Franklin,

If you're only going to join to rows, a simple join query will do the trick.  If you're going to join several rows you might want to investigate recursive SQL.

For this one, try this:

SELECT t0.acct_no, t0.seq_no, t1.seq_no, t0.message || coalesce (' ' || t1.message)
FROM mytable t0
LEFT JOIN mytable t1
  ON t0.acct_no = t1.acc_no
 AND t0.seq_no = 1
 AND t1.seq_no = 2;


Good Luck,
Kent

Author

Commented:
I cant hardcode t0.seq_no = 1, and t1.seq_no = 2; , it could be more or any number .
Simply put ill have to group by t0.acct_no and concat the message column based on the seq no

 
try

with a as (
 select acct_no, seq_no, message
 from your_table
 where seq_no = 1

union all
 select t1.acct_no, t2.seq_no, t1.message || t2.message
 from  a t1
   join your_table t2
     on t1.acct_no = t2.acct_no
    and t1.seq_no = t2.seq_no - 1
),
b as (
select *, row_number() over(partition by acct_no order by seq_no desc) rown
from   a
)
select acct_no, seq_no, message
from b
where rown=1
Hi Franklin,

Here's an EE article that explains the recursive SQL feature of DB2.  

  http://www.experts-exchange.com/Database/DB2/A_3618-Recursive-SQL-in-DB2-Converting-rows-to-columns.html

Momi has summarized it as a query that should work for you.



Kent

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial