asked on

Rows to list as columns

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

Kent Olsen

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

FranklinRaj22

ASKER

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

ASKER CERTIFIED SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kent Olsen

Hi Franklin,

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

https://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