CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
User87797
User87797

Reputation: 53

Merging two rows on to one in different columns

Firstly sorry if this has already been answered somewhere else, I have been unable to find an answer though after days of searching.

Is there a way to merge two rows into one row using different columns.

You will see from the image below, the row is identical, other than the date and location what I am looking for is to have the details below on one row. Where the date column is displayed twice with different column names for example 'Date sent to X location' and 'Date sent to Y location'. The location would not need to be displayed if we put the correct dates in the correct columns, as they would see what the location was from the column name.

enter image description here

So far I use this query, and I am unsure on how to adjust it to do what I need?

select 
l.lot_number,
trunc(l.start_tran_date) AS "Date sent to location",
l.location_id_2 AS "Location"
FROM t_tran_log l 
WHERE  
(l.location_id_2 = 'SENTTOMAP' OR l.location_id_2 = 'WAITINGFORCOLLECTION')
;

This is what I would like the above result to look like:

enter image description here

Upvotes: 0

Views: 96

Answers (2)

Lorenzo
Lorenzo

Reputation: 81

This would be my approach:

1- Create a test table

    create table MESSYLOG 
(
lotn varchar(20),
datesent date,
location varchar(20)
);


insert into messylog values ('abc', '06-JUN-16', 'waiting');
insert into messylog values ('abc', '07-JUN-16', 'sent');
insert into messylog values ('def', '08-JUN-16', 'waiting');
insert into messylog values ('def', '10-JUN-16', 'sent');

--select * from MESSYLOG

2- Write 2 subqueries

select t1.lotn,t2.DateWait, t1.DateSentmap, 
from
(
select e.lotn, e.datesent as DateSentmap
from messylog e
where e.location = 'sent'
) t1
JOIN 
 (
  select m.lotn,  m.datesent as DateWait 
from messylog m
where m.location = 'waiting'
)t2
on t1.lotn = t2.lotn

3-Resultset

        LOTN  DATEWAIT  DATESENTMAP
        abc  06-JUN-16  07-JUN-16
        def  08-JUN-16  10-JUN-16

Upvotes: 1

Glenn
Glenn

Reputation: 9170

If there is only a single date to a location for a given lot, could try something like this:

SELECT lot_number
      ,MAX(CASE WHEN location_id = 'WAITINGFORCOLLECTION' THEN start_tran_date) ELSE NULL END) AS "Date waiting for collection"
      ,MAX(CASE WHEN location_id = 'SENTTOMAP'            THEN start_tran_date) ELSE NULL END) AS "Date sent to map"
  FROM t_tran_log
  GROUP BY lot_number

The aggregate function (MAX) will skip the NULL column values leaving the single value for the location.

Upvotes: 0

Related Questions