Reporting values from multiple records in one row

Question

How can I displaying multiple records in one row in oracle without creating a stored procedure?

Data Set:

Name Job Update_date Armstrong IDE 07/02/2014 Armstrong IDE 05/02/2015 Armstrong IDEC 03/02/2016 Armstrong LT 14/02/2016 Beyonce LT 12/02/2015 Beyonce LTC 12/02/2016 Deadpool LT 05/02/2015

Desired Result:

Name IDE IDEC LT LTC Armstrong 05/02/2015 03/02/2016 14/02/2016 null Beyonce null null 12/02/2015 12/02/2016 Deadpool null null 05/02/2015 null

For each job I must have the biggest dates

The solution I see is to create a temporary table by Job and make union with name!!

MT0 · Accepted Answer · 2016-02-14 22:31:53Z

Oracle Setup:

CREATE TABLE Data_Set ( Name, Job, Update_date ) AS SELECT 'Armstrong', 'IDE', DATE '2014-02-07' FROM DUAL UNION ALL SELECT 'Armstrong', 'IDE', DATE '2015-02-05' FROM DUAL UNION ALL SELECT 'Armstrong', 'IDEC', DATE '2016-02-03' FROM DUAL UNION ALL SELECT 'Armstrong', 'LT', DATE '2016-02-14' FROM DUAL UNION ALL SELECT 'Beyonce', 'LT', DATE '2015-02-12' FROM DUAL UNION ALL SELECT 'Beyonce', 'LTC', DATE '2016-02-12' FROM DUAL UNION ALL SELECT 'Deadpool', 'LT', DATE '2015-02-05' FROM DUAL;

Query:

SELECT Name, MAX( CASE JOB WHEN 'IDE' THEN Update_Date END ) AS IDE, MAX( CASE JOB WHEN 'IDEC' THEN Update_Date END ) AS IDEC, MAX( CASE JOB WHEN 'LT' THEN Update_Date END ) AS LT, MAX( CASE JOB WHEN 'LTC' THEN Update_Date END ) AS LTC FROM Data_Set GROUP BY Name ORDER BY Name;

Results:

NAME IDE IDEC LT LTC --------- --------- --------- --------- --------- Armstrong 05-FEB-15 03-FEB-16 14-FEB-16 Beyonce 12-FEB-15 12-FEB-16 Deadpool 05-FEB-15

Paul · Accepted Answer · 2016-02-14 22:24:24Z

Something like this should do provided you know exactly which values can be found in Job. And in 11G or newer, yes.

SELECT Name, IDE, IDEC, LT, LTC FROM YOUR_TABLE PIVOT ( MAX(Update_date) FOR Job IN ('IDE' AS IDE, 'IDEC' AS IDEC, 'LT' AS LT, 'LTC' AS LTC))

Collectives™ on Stack Overflow

Reporting values from multiple records in one row

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related