CODEWITHSUNDEEP
sql
Matt
Matt

Reputation: 171

SQL: Split rows out of values from multiple columns

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.

Upvotes: 2

Views: 44

Answers (1)

Zakaria
Zakaria

Reputation: 4806

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;

Upvotes: 1

Related Questions