Home Ask Login Register

Developers Planet

Your answer is one click away!

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


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

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


Leave an answer

Quote of the day: live life