sqlnewb sqlnewb - 6 months ago 15
SQL Question

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!

Answer

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.

Comments