I didn't design it but I need to get data out of it.
-
So lets say I have a table (Ports) with an Identifier and then a compacted not XML field that is semi structured. select * from Ports --Yields this. ID Categories 1 Town:York, Street:Main, Phone:712-542-3423 The Categories field might have two fields in it. Town and Street or it might have 15. I need to get these out into separate fields as soo ID Town Street Phone 1 York Main 712...... ideas on SQL SQL server 2016 is what we are running. Ideas?
To err is human to really mess up you need a computer
-
So lets say I have a table (Ports) with an Identifier and then a compacted not XML field that is semi structured. select * from Ports --Yields this. ID Categories 1 Town:York, Street:Main, Phone:712-542-3423 The Categories field might have two fields in it. Town and Street or it might have 15. I need to get these out into separate fields as soo ID Town Street Phone 1 York Main 712...... ideas on SQL SQL server 2016 is what we are running. Ideas?
To err is human to really mess up you need a computer
Not particularly simple, since different rows could have different "attributes" within the categories column. Something like this should work:
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;
The
@querywill look something like:SELECT ID, [Phone],[Street],[Town]
FROM (SELECT ID, PropertyName, PropertyValue FROM #T) As T
PIVOT (Max(PropertyValue) FOR PropertyName In ([Phone],[Street],[Town])) As PSTRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs[^] Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Not particularly simple, since different rows could have different "attributes" within the categories column. Something like this should work:
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;
The
@querywill look something like:SELECT ID, [Phone],[Street],[Town]
FROM (SELECT ID, PropertyName, PropertyValue FROM #T) As T
PIVOT (Max(PropertyValue) FOR PropertyName In ([Phone],[Street],[Town])) As PSTRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs[^] Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
So lets say I have a table (Ports) with an Identifier and then a compacted not XML field that is semi structured. select * from Ports --Yields this. ID Categories 1 Town:York, Street:Main, Phone:712-542-3423 The Categories field might have two fields in it. Town and Street or it might have 15. I need to get these out into separate fields as soo ID Town Street Phone 1 York Main 712...... ideas on SQL SQL server 2016 is what we are running. Ideas?
To err is human to really mess up you need a computer
If you have the option of pulling the data into C# it would be a lot easier (likely) to change the data in C# and put it back into SQl.
Social Media - A platform that makes it easier for the crazies to find each other. Everyone is born right handed. Only the strongest overcome it. Fight for left-handed rights and hand equality.
-
So lets say I have a table (Ports) with an Identifier and then a compacted not XML field that is semi structured. select * from Ports --Yields this. ID Categories 1 Town:York, Street:Main, Phone:712-542-3423 The Categories field might have two fields in it. Town and Street or it might have 15. I need to get these out into separate fields as soo ID Town Street Phone 1 York Main 712...... ideas on SQL SQL server 2016 is what we are running. Ideas?
To err is human to really mess up you need a computer
Depending on how dynamic the table is (I would think Ports would be rather static) I would brute force the categories into separate table(s) and either use Richards pivot or a monstrous view with 30 left outer joins. Then when you get a new Ports just run the brute again to parse the Categories.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
