Solved

SQL multiple rows result data in Linear display

Posted on 2014-09-15
3
264 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

Title # Comments Views Activity
automatic email alert 1 52
case statement in where clause with not exist 15 52
Converting a row into a column 2 53
upgrading Oracle 10g/ 11g / 11g R2 to Oracle 12c 25 54
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

777 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