title: What's the most efficient way to normalize text from column into a table?
body: <p>In T-SQL I have a column with some text in it with a format like the following:</p>
  
  <pre><code>[Key1:Value1:Value2:Value3:Value4:Value5]
  [Key2:Value1:Value2:Value3:Value4:Value5]
  [Key3:Value1:Value2:Value3:Value4:Value5]
  </code></pre>
  
  <p>where there can be any number of bracket sets, but usually between 3 and 6. I'm looking for a way to quickly format them into a temp table or table variable so I can report on the data. For example, I'd want the table format to be:</p>
  
  <pre><code>|Key|Column 1|Column 2|Column 3|Column 4|Column 5|  
  |Key 1|Value 1|Value 2|Value 3|Value 4|Value 5|  
  |Key 2|Value 1|Value 2|Value 3|Value 4|Value 5|  
  |Key 3|Value 1|Value 2|Value 3|Value 4|Value 5|  
  </code></pre>
  
  <p>I know this is pushing the limits of SQL and should be handled through modification of the application, but I'm hoping there's something clever I can do with T-SQL for now. </p>