sql - SQL中如何将单行的数据拆分成多行？

Question

我有以下格式的表格

ProjectID           LocationID
1                   [1,2,3,4]
2                   [2,3]

我可以将 LocationID 列中的数据拆分为如下所示的多行吗？

ProjectID           LocationID
1                   1
1                   2
1                   3
1                   4
2                   2
2                   3

我只需要使用 SQL 将数据加载到 Power-Bi。是否可以？

标签： sqlsql-servertsqlsql-server-2014

Answer 1

如果 locationID 的数据类型是 varchar 则：

 create table projects (ProjectID int, LocationID varchar(50));
 insert into projects values(1,                  '[1,2,3,4]');
 insert into projects values(2,                  '[2,3]');

询问：

select projectid, value 
 from projects 
 CROSS APPLY STRING_SPLIT(replace(replace(locationid,'[',''),']',''),',')

输出：

投影	价值
1	1
1	2
1	3
1	4
2	2
2	3

db<小提琴在这里

SQL Server 2014 解决方案

 create table projects (ProjectID int, LocationID nvarchar(max));
 insert into projects values(1,                  '[1,2,3,4]');
 insert into projects values(2,                  '[2,3]');

询问：

     WITH tmp AS
(
    SELECT
        ProjectID,
        LEFT(replace(replace(locationid,'[',''),']',''), CHARINDEX(',', replace(replace(locationid,'[',''),']','') + ',') - 1) LocationID,
        STUFF(replace(replace(locationid,'[',''),']',''), 1, CHARINDEX(',', replace(replace(locationid,'[',''),']','') + ','), '') b
    FROM projects
    
    UNION all

    SELECT
        ProjectID,
        LEFT(b, CHARINDEX(',', b + ',') - 1),
        STUFF(b, 1, CHARINDEX(',', b + ','), '')
    FROM tmp
    WHERE
        b > ''
)

SELECT
    ProjectID, LocationID
FROM tmp
ORDER BY projectid
 

输出：

项目 ID	位置 ID
1	1
1	2
1	3
1	4
2	2
2	3

db<小提琴在这里