given:
ItemID | ItemProperty
4511 | Black / Yellow / Red
desired:
ItemID | ItemPropertySingleValue
4511 | Black
4511 | Yellow
4511 | Red
Since T-SQL doesn't have such a function by default, I searched a bit in the Internet and found a nice idea to use XML data type to do the job. Here is a T-SQL function, which takes as parameters key column value, string to split and separator and returns a table, containing of pairs key value + substring. Additionally an index/position of each substring in the original string is returned - in this way values form certain levels/depth can be selected.
CREATE FUNCTION [dbo].[splitCategoriesForItem]
(
-- Add the parameters for the function here
@pItemNr nvarchar(255), -- ID of the item
@pCategoriesString nvarchar(255), -- string of categories to split (in the case of pixi-export - column TonTrText
@pSeparator nvarchar(1)
)
RETURNS
@tblResult TABLE
(
PrdKey nvarchar(255), -- ID of the item
Value nvarchar(max), -- extracted value
valueIndex int IDENTITY(1,1) -- index/position of the value in a string
)
AS
BEGIN
WITH Vals AS (
SELECT
@pItemNr as ItemKey
,CAST('''' + REPLACE(@pCategoriesString, @pSeparator,
'' '') + '' '' AS XML) XmlColumn
) -- close CTE
INSERT INTO @tblResult
SELECT
_res.PrdKey
, _res.Value
FROM (
SELECT
ItemKey AS PrdKey
, LTRIM(RTRIM(C.value(''.'',''varchar(max)''))) AS Value
FROM Vals
CROSS APPLY Vals.XmlColumn.nodes(''/d'') AS T(C)
) _res
RETURN
END
Feel free to contact me in the case of questions.
