CODEWITHSUNDEEP
sql
TinTin
TinTin

Reputation: 13

2 column with same ID to 1 row

I have a table with only 2 column which is as follow

|ID  | Date       |
===================
|1   | 03/04/2017 |
|1   | 09/07/1997 |
|2   | 04/04/2014 |

I want to achieve an end result as follow

|ID  | Date 1     |Date 2      |
================================
|1   | 03/04/2017 | 09/07/1997 |
|2   | 04/04/2014 | NULL       |

I'm currently reading up on PIVOT function and I'm not sure am I on the right track. Am still new to SQL

Upvotes: 0

Views: 37

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

A simple pivot query should work here, with a twist. For your ID 2 data, there is only one row, but in this case you want to report a first date and a NULL second date. We can use a CASE expression to handle this case.

SELECT
    ID,
    MAX(Date) AS date_1,
    CASE WHEN COUNT(*) = 2 THEN MIN(Date) ELSE NULL END AS date_2
FROM yourTable
GROUP BY ID

Output:

enter image description here

Demo here:

Rextester

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93694

This can be done easily using min/max aggregate function 

select Id,min(Date),
        case when min(Date)<>max(Date) then max(Date) end
From yourtable 
Group by Id

If this will not help you with your original data, then alter sample data and expected result

Upvotes: 1

Related Questions