Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

SQL multiple rows result data in Linear display

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
raana jella
Asked:
raana jella
1 Solution
 
sdstuberCommented:
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
 
Steve WalesSenior Database AdministratorCommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now