Solved

Rows to list as columns

Posted on 2011-09-07
4
338 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Query 18 80
Oracle PL/SQL syntax 4 52
Oracle Pivot 2 32
get most recent and second most recent date in SQL Server 24 35
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now