title: Can I use SQL to split contents of a table column stored as CSV (comma separated values) into individual rows in a new table?
body: <p>I see there are a couple of related questions with answers, but not exactly what I need, so I'll ask a new question.  I have this CSV file with thousands of rows of store inventories data which I'd like to import into a MS SQL Server database and use SQL to process them. After importing the CSV file, the SQL table will have three columns that contain CSV data. The problem is that I need to get this CSV data into individual rows in order to analyze it more. I'd like to end up with two tables in the end: the original table created by the import of the CSV file, and a table created by splitting out the CSV. Here is a representation of what the two tables would look like:</p>
  
  <pre><code>/Table1 (the original CSV file).  First row is column names:
  StoreID,Date,StoreName,City,State,Category1CSV,Category2CSV,Category3CSV
  1051,2/16/2014,Easton,Columbus,OH,"Flour,Yeast,Baking Powder","Milk,Water,Oil","Cinnamon,Sugar"
  1425,1/14/2014,Crocker Park,Westlake,OH,"Baking Powder,Yeast,Flour","Oil,Milk,Water","Rosemay,Cinnamon,Sugar"
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/5X54S.jpg" alt="Table1"></p>
  
  <pre><code>/Table2 (after splitting the CSV column contents). First row is column names:
  StoreID,Date,StoreName,City,State,ItemName,ItemRank,ItemCategory
  1051,2/16/2014,Easton,Columbus,OH,Flour,1,1
  1051,2/16/2014,Easton,Columbus,OH,Yeast,2,1
  1051,2/16/2014,Easton,Columbus,OH,Baking Powder,3,1
  1051,2/16/2014,Easton,Columbus,OH,Milk,4,2
  1051,2/16/2014,Easton,Columbus,OH,Water,5,2
  1051,2/16/2014,Easton,Columbus,OH,Oil,6,2
  1051,2/16/2014,Easton,Columbus,OH,Cinnamon,7,3
  1051,2/16/2014,Easton,Columbus,OH,Sugar,8,3
  1425,1/14/2014,Crocker Park,Westlake,OH,Baking Powder,1,1
  1425,1/14/2014,Crocker Park,Westlake,OH,Yeast,2,1
  1425,1/14/2014,Crocker Park,Westlake,OH,Flour,3,1
  1425,1/14/2014,Crocker Park,Westlake,OH,Oil,4,2
  1425,1/14/2014,Crocker Park,Westlake,OH,Milk,5,2
  1425,1/14/2014,Crocker Park,Westlake,OH,Water,6,2
  1425,1/14/2014,Crocker Park,Westlake,OH,Rosemary,7,3
  1425,1/14/2014,Crocker Park,Westlake,OH,Cinnamon,8,3
  1425,1/14/2014,Crocker Park,Westlake,OH,Sugar,9,3
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/5TGjB.jpg" alt="Table2"></p>
  
  <p>The SQL column data types are:</p>
  
  <pre><code>Table 1
  StoreID - int
  Date - date
  StoreName - nvarchar(50)
  City- nvarchar(50)
  State- nvarchar(50)
  Category1CSV - nvarchar(MAX)
  Category2CSV - nvarchar(MAX)
  Category3CSV - nvarchar(MAX)
  
  Table2
  StoreID - int
  Date - date
  StoreName - nvarchar(50)
  City- nvarchar(50)
  State - nvarchar(50)
  ItemName - nvarchar(50)
  ItemRank - tinyint
  ItemCategory -tinyint
  </code></pre>
  
  <p>The Table 1 columns labeled Category1CSV, Category2CSV, and Category3CSV contents map to Table 2 columns: ItemName, ItemRank, ItemCategory, where ItemName is the Item (example: Flour), ItemRank is the order of the item in the CSV list, and ItemCategory is either 1,2 or 3, depending on whether the data came from Category1CSV, Category2CSV or Category3CSV.</p>
  
  <p>The most important aspect of this (other than splitting out the CSV column) is to maintain the order of items within the CSV columns. for example, StroreID 1051 has Category1CSV contents of "Flour,Yeast,Baking Powder". Those will map to the columns ItemName, ItemRank, and ItemCategory such that ItemName = Flour, it's ItemRank = 1, and the ItemCategory = 1. This would be the first row in Table 2. The second row would be ItemName = Yeast, it's ItemRank = 2, and the ItemCategory = 1, and so on until you end up with what looks like Table 2 above. Also, you'll notice that the ItemRank numbering starts with the contents of the column Category1CSV, then continues to Category2CSV and finally Category3CSV.</p>
  
  <p>After that lengthy explanation, is it possible to have some SQL statement that will create Table 2 from Table 1 for me? If so, what would that look like? I'm planning to use MS SQL Server Express 2012.</p>
  
  <p>OR... as someone suggest to me, it may be best to have some VBA in Excel or Python script (maybe in conjunction with Notepad++?) to accomplish this, then just import the final data?  I don't care either way, I just can't keep manually editing the CSV file, as it's very tedious and time consuming.</p>