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?
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