How to split the data from a single row into multiple rows in SQL Server?

Question

I have a table in the following format

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

Can I split the data in the column LocationID into multiple rows like below?

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

I need to get the data loaded to Power-Bi using the SQL only. is it possible?

Answer 1

If data type of locationID is varchar then:

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

Query:

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

Output:

projectid	value
1	1
1	2
1	3
1	4
2	2
2	3

db<fiddle here

Solution for 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]');

Query:

     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
 

Output:

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

db<fiddle here