SQL: Split rows out of values from multiple columns

Question

I have a table that looks something like this:

ID	State Name	State Value	City Name	City Value
1	Indiana	8.0	Gary	5.0
2	Florida	9.0	Miami	2.0

I would like to output a table that looks something like this:

ID	Name	Value
1	State, Indiana	8.0
2	City, Gary	5.0
3	State, Florida	9.0
4	City, Miami	2.0

I was trying to solve by groupby but that is just outputting unique rows in their entirety not creating unique rows based off multiple columns unique values.

Answer 1

You can use union all:

select concat('State, ', StateName) as Name, StateValue as Value
from table_name
union all
select concat('City, ', CityName) as Name, CityValue as Value
from table_name;