如何在SQL Server中使用纯SQL从CSV源创建虚拟表

适用场景 #

• 无需存储过程，纯SQL即时执行
• 可直接接入第三方报表引擎
• 支持SQL Server 2016及以上版本

兼容性最优方案（支持SQL Server 2016~所有最新版本） #

利用SQL Server原生JSON解析能力实现按位置提取CSV字段，逻辑和PostgreSQL的split_part完全对齐：

SELECT
    JSON_VALUE(CONCAT('["', REPLACE(p.Lines, ',', '","'), '"]'), '$[0]') AS Col1,
    JSON_VALUE(CONCAT('["', REPLACE(p.Lines, ',', '","'), '"]'), '$[1]') AS Col2,
    JSON_VALUE(CONCAT('["', REPLACE(p.Lines, ',', '","'), '"]'), '$[2]') AS Col3,
    JSON_VALUE(CONCAT('["', REPLACE(p.Lines, ',', '","'), '"]'), '$[3]') AS Col4
FROM (
    SELECT value AS Lines
    -- 兼容Windows/Linux换行符，先去除\r再按\n拆分行
    FROM STRING_SPLIT(REPLACE(:csvdata, CHAR(13), ''), CHAR(10), 1)
    -- 过滤空行，不需要可删除
    WHERE TRIM(value) != ''
) p

逻辑说明 #

• 第一步先把整段CSV文本按换行符拆分，得到每行的独立数据，第三个参数1为enable_ordinal，仅SQL Server 2022及以上支持，可保证行顺序和原始CSV完全一致，低版本可删除该参数
• 第二步把单行CSV的逗号替换为","，前后拼接JSON数组标识，将单行CSV转换为标准JSON数组格式
• 第三步用JSON_VALUE按索引提取对应字段，JSON索引从0开始，对应PostgreSQL split_part的position参数减1即可

高版本专属方案（仅支持SQL Server 2022及以上） #

利用STRING_SPLIT原生返回字段位置的能力实现，适合CSV字段包含双引号的场景：

SELECT
    MAX(CASE WHEN s2.ordinal = 1 THEN s2.value END) AS Col1,
    MAX(CASE WHEN s2.ordinal = 2 THEN s2.value END) AS Col2,
    MAX(CASE WHEN s2.ordinal = 3 THEN s2.value END) AS Col3,
    MAX(CASE WHEN s2.ordinal = 4 THEN s2.value END) AS Col4
FROM (
    SELECT value AS Lines, ordinal AS line_no
    FROM STRING_SPLIT(REPLACE(:csvdata, CHAR(13), ''), CHAR(10), 1)
    WHERE TRIM(value) != ''
) p
CROSS APPLY STRING_SPLIT(p.Lines, ',', 1) s2
GROUP BY p.line_no
ORDER BY p.line_no

注意事项 #

以上方案仅适用于无转义字符的普通CSV格式，若CSV字段中包含逗号、双引号等转义字符，需要额外增加转义处理逻辑。

内容的提问来源于stack exchange，提问作者Scott Ali