Database

jan Meeling wrote:

I was trying that with no results.

jan Meeling wrote:

Usually we also frown on "SELECT *",

jan Meeling wrote:

and weirdly, you use a "using" clause for the connection but not for the disposable command.

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

DROP TABLE IF EXISTS #T;

CREATE TABLE #T
(
    ID int, 
    PropertyName varchar(50),
    PropertyValue varchar(50)
);

INSERT INTO #T
(
    ID,
    PropertyName,
    PropertyValue
)
SELECT
    T.ID,
    LTRIM(LEFT(V.value, CHARINDEX(':', V.value) - 1)) As PropertyName,
    LTRIM(SUBSTRING(V.value, CHARINDEX(':', V.value) + 1, LEN(V.Value))) As PropertyValue
FROM
    YourTable As T
    CROSS APPLY string_split(T.Categories, ',') As V
;

DECLARE @columns nvarchar(max) = STUFF
(
    (
        SELECT DISTINCT ',' + QUOTENAME(PropertyName) 
        FROM #T 
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)')
    , 1, 1, ''
);

DECLARE @query nvarchar(max) = N'SELECT ID, ' + @columns 
    + N' FROM (SELECT ID, PropertyName, PropertyValue FROM #T) As T'
    + N' PIVOT (Max(PropertyValue) FOR PropertyName In (' + @columns + N')) As P';

EXECUTE (@query);

DROP TABLE #T;

SELECT ID, [Phone],[Street],[Town] 
FROM (SELECT ID, PropertyName, PropertyValue FROM #T) As T 
PIVOT (Max(PropertyValue) FOR PropertyName In ([Phone],[Street],[Town])) As P