我可以使用 SQL 将存储为 CSV（逗号分隔值）的表列的内容拆分为新表中的各个行吗？

我看到有几个相关的问题和答案，但不完全是我需要的，所以我会问一个新问题。我有一个包含数千行商店库存数据的 CSV 文件，我想将其导入 MS SQL Server 数据库并使用 SQL 来处理它们。导入 CSV 文件后，SQL 表将具有包含 CSV 数据的三列。问题是我需要将此 CSV 数据放入单独的行中以便进行更多分析。我希望最终得到两个表：通过导入 CSV 文件创建的原始表和通过拆分 CSV 创建的表。下面是这两个表的样子：

/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"

/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

SQL 列数据类型有：

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

表 1 中标记为 Category1CSV、Category2CSV 和 Category3CSV 内容的列映射到表 2 列：ItemName、ItemRank、ItemCategory，其中 ItemName 是项目（例如：面粉），ItemRank 是项目在 CSV 列表中的顺序，ItemCategory 是1,2 或 3，具体取决于数据是来自 Category1CSV、Category2CSV 还是 Category3CSV。

最重要的方面（除了拆分 CSV 列之外）是维护 CSV 列中项目的顺序。例如，StroreID 1051 的 Category1CSV 内容为“面粉、酵母、发酵粉”。这些将映射到 ItemName、ItemRank 和 ItemCategory 列，使得 ItemName = Flour，它的 ItemRank = 1，并且 ItemCategory = 1。这将是表 2 中的第一行。第二行将是 ItemName = Yeast，它的 ItemRank = 2，ItemCategory = 1，依此类推，直到最终得到如上表 2 所示的内容。此外，您还会注意到 ItemRank 编号从 Category1CSV 列的内容开始，然后继续到 Category2CSV，最后是 Category3CSV。

经过冗长的解释后，是否有可能有一些 SQL 语句为我从表 1 创建表 2？如果是这样，那会是什么样子？我计划使用 MS SQL Server Express 2012。

或者...正如有人向我建议的那样，最好在 Excel 或 Python 脚本中使用一些 VBA（也许与 Notepad++ 结合使用？）来完成此操作，然后导入最终数据？不管怎样，我都不在乎，我只是不能继续手动编辑 CSV 文件，因为它非常乏味且耗时。

我将使用 Split 函数来分割附加值。

我使用的 Split 函数使用数字（包含数字 1 到 1,000,000 的表格）来促进拆分过程。

一旦 Numbers 表和 Split 函数就位，我将使用 CROSS APPLY 函数将 Split 应用于 CSV 列。分割 CSV 列的代码如下所示（这只是基于您提供的数据的几个测试行）。

DECLARE @Table TABLE (val1 VARCHAR(50), val2 VARCHAR(50), val3 VARCHAR(50), csv1 VARCHAR(100), csv2 VARCHAR(100), csv3 VARCHAR(100))
INSERT INTO @Table
VALUES ('Easton', 'Columbus', 'OH', 'Flour,Yeast,Baking Powder','Milk,Water,Oil','Cinnamon,Sugar')
 , ('Crocker Park', 'Westlake', 'OH', 'Baking Powder,Yeast,Flour','Oil,Milk,Water','Rosemary,Cinnamon,Sugar')

SELECT tbl.val1, val2, val3, apl.*
  FROM @Table tbl
 CROSS APPLY(
    SELECT val
      FROM dbo.Split(tbl.csv1, ',')
   ) apl

UNION ALL

SELECT tbl.val1, val2, val3, apl.*
  FROM @Table tbl
 CROSS APPLY(
    SELECT val
      FROM dbo.Split(tbl.csv2, ',')
   ) apl

UNION ALL

SELECT tbl.val1, val2, val3, apl.*
  FROM @Table tbl
 CROSS APPLY(
    SELECT val
      FROM dbo.Split(tbl.csv3, ',')
   ) apl
 ORDER BY val1

根据样本日期，其输出将如下所示。

Crocker Park    Westlake    OH  Baking Powder
Crocker Park    Westlake    OH  Cinnamon
Crocker Park    Westlake    OH  Flour
Crocker Park    Westlake    OH  Milk
Crocker Park    Westlake    OH  Oil
Crocker Park    Westlake    OH  Rosemary
Crocker Park    Westlake    OH  Sugar
Crocker Park    Westlake    OH  Water
Crocker Park    Westlake    OH  Yeast
Easton          Columbus    OH  Baking Powder
Easton          Columbus    OH  Cinnamon
Easton          Columbus    OH  Flour
Easton          Columbus    OH  Milk
Easton          Columbus    OH  Oil
Easton          Columbus    OH  Sugar
Easton          Columbus    OH  Water
Easton          Columbus    OH  Yeast

这是创建 Numbers 表的代码

DECLARE @tbl TABLE (n INT)
INSERT INTO @tbl (n)
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

; WITH Num AS(
SELECT one.n
  FROM @tbl one
 CROSS JOIN @tbl two
 CROSS JOIN @tbl three
 CROSS JOIN @tbl four
 CROSS JOIN @tbl five
 CROSS JOIN @tbl six
)
SELECT ROW_NUMBER() OVER(ORDER BY n) AS n
  INTO dbo.Numbers
  FROM Num

ALTER TABLE dbo.Numbers
ALTER COLUMN n INT NOT NULL

ALTER TABLE dbo.Numbers 
ADD PRIMARY KEY (n)
GO

最后，这是创建 Split 函数的代码。

CREATE FUNCTION dbo.Split
(
@List VARCHAR(MAX),
@Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
RETURN
(
SELECT val = SUBSTRING(@List, n, CHARINDEX(@Delimiter, @List + @Delimiter, n) - n)
  FROM dbo.Numbers
 WHERE n <= CONVERT(INT, LEN(@List)) 
   AND SUBSTRING(@Delimiter + @List, n, LEN(@Delimiter)) = @Delimiter
 );
GO