CODEWITHSUNDEEP
sqloracle-database
Sachin Kumar
Sachin Kumar

Reputation: 463

Fetch multiple row values as column values

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.

Upvotes: 1

Views: 81

Answers (1)

Debabrata
Debabrata

Reputation: 162

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));

Upvotes: 1

Related Questions