How to turn Columns into Rows

If i have a table that looks like this:

    ID int
    Name varchar
    City1 varchar
    City2 varchar
    City3 varchar
    State1 varchar
    State2 varchar
    State3 varchar

I understand a normal SELECT statement it will return data like this:

    ID, Name, City1, City2, City3, State1, State2, State3

B开发者_StackOverflowut how do i return the SELECT statement to return the multiple City/State's into different rows, and group them... So it looks like this:

    ID, Name, City, State

And instead of these being columns: Color1,Color2,Color3 and State1,State2,State3

They would be just more rows in the SELECT statement where all other data remains the same, if 1,2,3 Is Not Null.

Any suggestions on how to approach this using the least amount of Code? Preferrably one SELECT statement that handles this all?

---

You can query your table three times and use union all to combine the result.

select ID, Name, City1 as City, State1 as State
from YourTable
union all
select ID, Name, City2, State2
from YourTable
union all
select ID, Name, City3, State3
from YourTable

You can also do it in one query using a cross join and a couple of case statements.

select ID, 
       Name,
       case T.N 
         when 1 then City1
         when 2 then City2
         when 3 then City3
       end as City,
       case T.N 
         when 1 then State1
         when 2 then State2
         when 3 then State3
       end as State
from YourTable
  cross join (values(1),(2),(3)) as T(N)