CODEWITHSUNDEEP
mysqlsql
Coaltergeist
Coaltergeist

Reputation: 1

What is the best way to make a new column from pre-existing rows in SQL?

So I have a table that looks like this:

thing date date_type
1 1/12 start_date
1 1/14 end_date
2 2/14 start_date
2 2/15 end_date

And I'm trying to compress everything down to look like this:

thing start_date end_date
1 1/12 1/14
2 2/14 2/15

What is the best way to write this query? I'm relatively new to SQL so I'm not sure what the best way to word this is. I was thinking something like this:

SELECT thing, date AS start_date, date AS end_date
FROM t1 ON...

I'm sure there's a simple way to do this, I'm just unfamiliar with SQL.

Upvotes: 0

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Just use conditional aggregation:

select thing,
       max(case when data_type = 'start_date' then date end) as start_date,
       max(case when data_type = 'end_date' then date end) as end_date
from t
group by thing;

Upvotes: 1

Related Questions