Solved

SQL multiple rows result data in Linear display

Posted on 2014-09-15
3
270 Views
Last Modified: 2014-11-16
I have data in mutiple tables

Table A

Asset ID   Asset Date    Asset Status   Instance
A00001    08/01/2014    A                        1001
A00002    08/01/2014    A                        1002
A00003   08/02/2014     A                         1003

Table B

Instance      Instance Status       Approver ID   Requestor ID
1001                   P                            APPR1              RQST1
1001                   P                            APPR2              RQST1
1001                   P                            APPR3              RQST1
1002                   P                            APPR4              RQST2
1002                   P                            APPR5              RQST2
1002                   P                            APPR6              RQST3


I would like to see the details has below

Asset ID   Asset Date    Asset Status     Instance    Requestor  Approver1  Approver2 Appover3
A00001    08/01/2014    A                        1001          RQST1          APPR1         APPR2         APPR3
A00002    08/01/2014    A                        1002          RQST2          APPR4         APPR5         APPR6
A00003   08/02/2014     A                        1003
0
Comment
Question by:raana jella
[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
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40323357
you can't have completely dynamic columns, the number of columns must be known at the time the query is parsed (i.e. before it actually executes)

but for a fixed set of 1 requestor and 3 approvers try this

  SELECT asset_id,
         asset_date,
         asset_status,
         instance,
         MAX(CASE WHEN rn = 1 THEN requestor_id END) requestor,
         MAX(CASE WHEN rn = 1 THEN approver_id END) approver1,
         MAX(CASE WHEN rn = 2 THEN approver_id END) approver2,
         MAX(CASE WHEN rn = 3 THEN approver_id END) approver3
    FROM (SELECT a.asset_id,
                 a.asset_date,
                 a.asset_status,
                 a.instance,
                 b.requestor_id,
                 b.approver_id,
                 ROW_NUMBER() OVER(PARTITION BY a.instance ORDER BY b.approver_id) rn
            FROM tablea a LEFT OUTER JOIN tableb b ON a.instance = b.instance)
GROUP BY asset_id,
         asset_date,
         asset_status,
         instance
ORDER BY asset_id
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40445489
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Suggested Courses

623 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