Jasmine B. February 2016

SQL query: Separate column where IDs are the same but type is different

Basically I get this from a simple select query:

SELECT  Site.RefID, SiteName, Dates.Date, Dates.Type
FROM Site, Dates
WHERE Site.RefID = Dates.RefID; 

RefID | SiteName | Date        | Type
1       Sydney       06-12-15   OPENED
1       Sydney       08-12-15   CLOSED
2       Mel          17-12-15   OPENED
2       Mel          19-12-15   CLOSED

But I want to seperate it so tge result is similar to this:

RefID | SiteName | DateOPENED     | DateCLOSED
1       Sydney       06-12-15     | 08-12-15

Basically I want to compare the data tracking details

Apologies in advance if this question isn't structured very well :/ I'm a complete beginner

I was thinking maybe a select within a select or possible case when's, but I can't seem to get either working

Answers


jarlh February 2016

Add a GROUP BY to your current query, use MIN for opening date, and MAX for en closing date.

SELECT  Site.RefID, SiteName, MIN(Dates.Date) as DateOPENED, MIN(Dates.Date) as DateCLOSED
FROM Site
  JOIN Dates ON Site.RefID = Dates.RefID
group by fID, SiteName

Alternatively, JOIN once for opening and once for closing:

SELECT  Site.RefID, SiteName, do.Date as DateOPENED, dc.Date as DateCLOSED
FROM Site
  LEFT JOIN (select Refid, Date from Dates where Type = 'OPENED') do ON Site.RefID = do.RefID
  LEFT JOIN (select Refid, Date from Dates where Type = 'CLOSED') dc ON Site.RefID = dc.RefID


Giorgos Betsos February 2016

You can use conditional aggregation to get expected result:

SELECT  Site.RefID, SiteName, 
        MIN(CASE WHEN Dates.Type = 'OPENED' THEN Dates.Date END) DateOPENED,
        MAX(CASE WHEN Dates.Type = 'CLOSED' THEN Dates.Date END) DateCLOSED
FROM Site
INNER JOIN Dates ON Site.RefID = Dates.RefID
GROUP BY Site.RefID, SiteName 

Also, it is always preferable to use explicit instead of implicit join syntax.


potashin February 2016

Try the following approach, using case expression:

select s.RefID
     , s.Name
     , min(case when d.Type == 'OPENED' then d.Date end) as DateOPENED
     , min(case when d.Type == 'CLOSED' then d.Date end) as DateCLOSED
from Site s 
join Dates d on s.RefID = d.RefID
group by s.RefID, s.Name


SouravA February 2016

SELECT A.RefId, A.SiteName, A.Date DateOpened, B.Date DateClosed
FROM #tbl A JOIN #tbl B
ON A.RefId = B.RefID
AND A.Type = 'OPENED'
AND B.Type = 'CLOSED'

For the sake of simplicity, have replaced the query with #tbl(you can deal with it howsoever you'd like to).

Post Status

Asked in February 2016
Viewed 3,275 times
Voted 12
Answered 4 times

Search




Leave an answer