Fetch multiple row values as column values

Question

I have two tables MASTER_TABLE and DOCUMENTS table. Both are related with a column reference_id. DOCUMENTS table has columns doc_id,doc_type and doc_creation_date We can have more than one entry in DOCUMENTS table for each doc_type with different doc_creation_date. My aim is to fetch the doc_type and doc_id for each doc_type in a single row for the max doc_creation_date

MASTER_TABLE

REFERENCE_ID    COLUMN1 COLUMN2
     1            DATA1  DATA2
     2            DATA3  DATA4
     3            DATA5  DATA6



DOCUMENTS

REFERENCE_ID    DOC_ID  DOC_TYPE    DOC_CREATION_DATE
     1            11      PDF           16/06/2017
     1            12      XLS           16/06/2017
     1            13      TXT           16/06/2017
     1            14      PDF           15/06/2017
     1            15      XLS           15/06/2017
     1            16      TXT           15/06/2017
     2            17      PDF           16/06/2017
     2            18      XLS           16/06/2017
     2            19      TXT           16/06/2017




EXPECTED OUTPUT

REFERENCE_ID    DOC_ID_PDF  DOC_ID_XLS  DOC_ID_TXT
     1             11           12          13
     2             17           18          19

Is this possible to achieve using a single query. I tried a self join and pivot but guess am doing it the wrong way. We have Oracle 11g and 12c databases that we can use.

What if 2 doc_ids have same creation_date? e.g. doc_id 21 & 22 of ref_id = 1 and doc_type=pdf have creation_date=16-Jun-2017, which one should get picked up? — Debabrata
– Debabrata, Commented Jun 16, 2017 at 9:36
One ref Id will never have 2 doc id for same date. They differ atleast in seconds column which I did not provide in question for simplicity purpose. At any point in time there will exist only one doc id for each doc type for each ref Id. — Sachin Kumar
– Sachin Kumar, Commented Jun 16, 2017 at 12:16
@Debabrata: Thank you for the answer. Works perfect and now I understand the use of Rank() and partition by though I have seen it being used in many places. Thanks again. — Sachin Kumar
– Sachin Kumar, Commented Jun 18, 2017 at 2:09

Debabrata · Accepted Answer · 2017-06-16 09:53:35Z

Rank() Over([Partition by] Order by) followed by Pivot will solve your issue. Following is the code. The following will get you the max doc_id in case multiple doc_ids have same max creation date. If you need the minimum doc_id for the above scenario, change the max function to min in pivot clause.

WITH master_table AS
     (SELECT 1 reference_id, 'DATA1' column1, 'DATA2' column2
        FROM DUAL
      UNION
      SELECT 2 reference_id, 'DATA3' column1, 'DATA4' column2
        FROM DUAL
      UNION
      SELECT 3 reference_id, 'DATA5' column1, 'DATA6' column2
        FROM DUAL),
     documents AS
     (SELECT 1 reference_id, 11 doc_id, 'PDF' doc_type,
             '16/06/2017' doc_creation_date
        FROM DUAL
      UNION
      SELECT 1 reference_id, 12 doc_id, 'XLS' doc_type,
             '16/06/2017' doc_creation_date
        FROM DUAL
      UNION
      SELECT 1 reference_id, 13 doc_id, 'TXT' doc_type,
             '16/06/2017' doc_creation_date
        FROM DUAL
      UNION
      SELECT 1 reference_id, 14 doc_id, 'PDF' doc_type,
             '15/06/2017' doc_creation_date
        FROM DUAL
      UNION
      SELECT 1 reference_id, 15 doc_id, 'XLS' doc_type,
             '15/06/2017' doc_creation_date
        FROM DUAL
      UNION
      SELECT 1 reference_id, 16 doc_id, 'TXT' doc_type,
             '15/06/2017' doc_creation_date
        FROM DUAL
      UNION
      SELECT 2 reference_id, 17 doc_id, 'PDF' doc_type,
             '16/06/2017' doc_creation_date
        FROM DUAL
      UNION
      SELECT 2 reference_id, 18 doc_id, 'XLS' doc_type,
             '16/06/2017' doc_creation_date
        FROM DUAL
      UNION
      SELECT 2 reference_id, 19 doc_id, 'TXT' doc_type,
             '16/06/2017' doc_creation_date
        FROM DUAL)
SELECT *
  FROM (SELECT reference_id, doc_id, doc_type
          FROM (SELECT x.reference_id, y.doc_id, y.doc_type,
                       y.doc_creation_date,
                       RANK () OVER (PARTITION BY x.reference_id, y.doc_type ORDER BY y.doc_creation_date DESC)
                                                                         RANK
                  FROM master_table x JOIN documents y
                       ON (x.reference_id = y.reference_id)
                       )
         WHERE RANK = 1)
  pivot (max(doc_id)
  FOR doc_type
  IN ( 'PDF' doc_id_pdf, 'XLS'  doc_id_xls,'TXT'  doc_id_txt));

Collectives™ on Stack Overflow

Fetch multiple row values as column values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related