Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# SQL multiple rows result data in Linear display

Posted on 2014-09-15
Medium Priority
271 Views
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
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

LVL 74

Accepted Solution

sdstuber earned 2000 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 23

Expert Comment

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

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
###### Suggested Courses
Course of the Month6 days, 2 hours left to enroll