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.
CodePudding user response：
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;