Search code examples
mysqljoinunpivot

MySQL Join 2 columns from table y into rows of table x

Trying to wrap my mind around how to write this SQL query.

Table X has 3 Columns: Year, ID, Value and looks like so

Year   |   ID   |     Value
2013       101        10000
2014       101        11000
2015       101        12000
2013       102        7000
2014       102        8000
2015       102        9000

And table Y has 3 Columns: ID, Curr_Year_Val, Next_Year_Val and looks like this

ID   |  Curr_Year_Val   | Next_Year_Val
101        13000                  14000
102        6000                   5000

I would like to write a select statement to join these two tables together, but maintain the layout of Table X, like so:

Year           |    ID    |    Value
2013               101         10000
2014               101         11000
2015               101         12000
Curr_Year_Val      101         13000
Next_Year_Val      101         14000

Is there a way to achieve this result? I've figured out how to just do a left join to add the columns from table y to table x, but would rather have the columns from table y unpivoted to the rows of table x. Thanks much in advance - this seems like it should be so easy, I've been googling for hours but I'm probably not using the proper terminology for what I'm trying to do in my searches.

Thanks!

Solution

  • Sounds like you should use union all:

    select year, id, value from x
union all
select 'curr_year_val', id, curr_year_val from y
union all
select 'next_year_val', id, next_year_val from y
order by 2, 1

    BTW, other databases would require you to have the same data types for all columns when using union. This works though with mysql.