Solved

Rows to list as columns

Posted on 2011-09-07
4
350 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:FranklinRaj22
  • 2
4 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 36495186
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
0
 

Author Comment

by:FranklinRaj22
ID: 36495290
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

 
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 36495377
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
0
 
LVL 45

Expert Comment

by:Kdo
ID: 36495562
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
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Join multiple pivot queries 2 16
TSQL  - Is there any way to return multiple rows on one matching 14 24
convert null in sql server 12 34
VB.net and sql server 4 35
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question