[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • 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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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