[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Rows to list as columns

Posted on 2011-09-07
4
Medium Priority
?
373 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
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 2000 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 46

Expert Comment

by:Kent Olsen
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

650 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