title: SQL Server split a single column multiple times
body: <p>I have a database table that has a column with stacked data with two levels with a column that I want to break a part. Here is the example of the data (data changed to protect the innocent :) :</p>
  
  <h3>Table</h3>
  
  <pre><code>ID = varchar(100)
  CarData = varchar(1000)
  
  ID       CarData
  1        Nissan:blue:20000,Ford:green:10000
  2        Nissan:steel:20001,Ford:blue:10001,Chevy:blue:10000,Ford:olive:10000
  ** Note that cardata can is not fixed, and can have many cars in it
  </code></pre>
  
  <h3>Output Desired:</h3>
  
  <pre><code>ID   Manufacture    Color     Cost
  1    Nissan         Blue      20000
  1    Ford           green     10000
  2    Nissan         steel      20001
  ... and on
  </code></pre>
  
  <p>So to say it plainly I need to break the first stacked field which is a comma and create a row for that, then break the second stacked field which is a colon into columns.</p>
  
  <p>Any help would be greatly appreciated.</p>