title: How to turn Columns into Rows
body: <p>If i have a table that looks like this:</p>
  
  <pre><code>    ID int
      Name varchar
      City1 varchar
      City2 varchar
      City3 varchar
      State1 varchar
      State2 varchar
      State3 varchar
  </code></pre>
  
  <p>I understand a normal SELECT statement it will return data like this:</p>
  
  <pre><code>    ID, Name, City1, City2, City3, State1, State2, State3
  </code></pre>
  
  <p>But 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:</p>
  
  <pre><code>    ID, Name, City, State
  </code></pre>
  
  <p>And instead of these being columns:  Color1,Color2,Color3 and State1,State2,State3 </p>
  
  <p>They would be just more rows in the SELECT statement where all other data remains the same, if 1,2,3 Is Not Null.</p>
  
  <p>Any suggestions on how to approach this using the least amount of Code? Preferrably one SELECT statement that handles this all?</p>