Improve company productivity with a Business Account.Sign Up

x
?
Solved

Rows to list as columns

Posted on 2011-09-07
4
Medium Priority
?
383 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 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
From store locators to asset tracking and route optimization, learn how leading companies are using Google Maps APIs throughout the customer journey to increase checkout conversions, boost user engagement, and optimize order fulfillment. Powered …

607 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