Does anyone have script that can pull out data held in the columns to split out the servernames in to their own columns?
| ID| Servers
|:-:|---------------------------------------------------------------------------------
| 1 | {"Name":"SQL-Vlfflk43E"}
| 2 | {"Name":"SQL-VgflkglkdA"},{"Name":"SQL-VCkfkjgitrE"},{"Name":"SQL-;bv;b;b"},{"Name":"SQL-kkkbdddb"}
| 3 | {"Name":"SQL-VgkgkgA"},{"Name":"SQL-VfkgkjygtbB"},{"Name":"SQL-lglg"}
| 4 | {"Name":"SQL-VotoevB"},{"Name":"SQL-VCfkjfkjrtrE"},{"Name":"SQL-lglkgl"}
| 5 | {"Name":"SQL-VblgltotA"},{"Name":"SQL-VCfkfkgE"},{"Name":"SQL-lkgkjgkg"}
| 6 | {"Name":"SQL-VCggkgkg"}
So ID 1 & 2 would become like the below? I know char & patindex can help just struggling to get my head around it.
| ID| Text | Server1 | Server2 | Server3 | Server4
| 1 | {"Name":"SQL-Vlfflk43E"} |SQL-Vlfflk43E |null |null | null
| 2 | {"Name":"SQL-VgflkglkdA"},{"Name":"SQL-VCkfkjgitrE"},{"Name":"SQL-kkkbvb;b"},{"Name":"SQL-kkkbdddb"}|SQL-VgflkglkdA|SQL-VCkfkjgitrE|SQL-kkkbvb | SQL-kkkbdddb
You may transform the stored data into a valid JSON array and parse it with JSON_VALUE():
SELECT
ID,
Server1 = JSON_VALUE(CONCAT('[', Servers, ']'), '$[0].Name'),
Server2 = JSON_VALUE(CONCAT('[', Servers, ']'), '$[1].Name'),
Server3 = JSON_VALUE(CONCAT('[', Servers, ']'), '$[2].Name'),
Server4 = JSON_VALUE(CONCAT('[', Servers, ']'), '$[3].Name')
FROM (VALUES
(1, '{"Name":"SQL-Vlfflk43E"}'),
(2, '{"Name":"SQL-VgflkglkdA"},{"Name":"SQL-VCkfkjgitrE"},{"Name":"SQL-;bv;b;b"},{"Name":"SQL-kkkbdddb"}'),
(3, '{"Name":"SQL-VgkgkgA"},{"Name":"SQL-VfkgkjygtbB"},{"Name":"SQL-lglg"}'),
(4, '{"Name":"SQL-VotoevB"},{"Name":"SQL-VCfkjfkjrtrE"},{"Name":"SQL-lglkgl"}'),
(5, '{"Name":"SQL-VblgltotA"},{"Name":"SQL-VCfkfkgE"},{"Name":"SQL-lkgkjgkg"}'),
(6, '{"Name":"SQL-VCggkgkg"}')
) v (ID, Servers)
Result:
ID Server1 Server2 Server3 Server4
-------------------------------------------------------------
1 SQL-Vlfflk43E
2 SQL-VgflkglkdA SQL-VCkfkjgitrE SQL-;bv;b;b SQL-kkkbdddb
3 SQL-VgkgkgA SQL-VfkgkjygtbB SQL-lglg
4 SQL-VotoevB SQL-VCfkjfkjrtrE SQL-lglkgl
5 SQL-VblgltotA SQL-VCfkfkgE SQL-lkgkjgkg
6 SQL-VCggkgkg