Solved

SQL multiple rows result data in Linear display

Posted on 2014-09-15
3
261 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
3 Comments
 
LVL 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

911 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