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.
>Solution :
You can use union all:
select concat('State, ', StateName) as Name, StateValue as Value
from table_name
union all
select concat('State, ', CityName) as Name, CityValue as Value
from table_name;