CODEWITHSUNDEEP
sql-servert-sql
Stephan
Stephan

Reputation: 43

Splitting a string and inserting the parts in the correct columns inside a table

I am trying to build a fact table with sql server. It currently looks like this:

[Item]  [Variant Descr.] [Variant Order] [Dim_Colour] [Dim_Size] [Dim_Style]
----------------------------------------------------------------------------
01         NAVY/44        COLOUR/SIZE     NULL         NULL       NULL
02         BLACK/S4       COLOUR/STYLE    NULL         NULL       NULL

I need to split the String in [Variant Descr.] and insert the parts into the correct Dim_ Column so the table will eventually look like this:

[Item]  [Variant Descr.] [Variant Order] [Dim_Colour] [Dim_Size] [Dim_Style]
----------------------------------------------------------------------------
01         NAVY/44        COLOUR/SIZE     NAVY         44       NULL
02         BLACK/S4       COLOUR/STYLE    BLACK        NULL     S4

The problem is that the parts of [Variant Order] and the number of parts of [Variant Descr.] may vary for each row. So I basically need to do something like:

  1. Get the String before the '/' in [Variant Descr.]
  2. Get the String before the '/' in [Variant Order]
  3. Insert the first value into the column specified by the second value
  4. Do this for all parts of [Variant Descr.]
  5. Do this for every row in the table

I have already tried to solve this with a user-defined function, only to find out that I cannot use dynamic SQL within a UDF.

Any help would be greatly appreciated

Cheers!

Upvotes: 0

Views: 209

Answers (4)

Zhorov
Zhorov

Reputation: 29943

One possible approach is to split the texts in [Variant Descr.] and [Variant Order] columns and update the table with dynamic statement. Although using STRING_SPLIT() is the first choice starting with SQL Server 2016, this function is not an option in this case, because the order of the substrings is not guaranteed. A working solution is to use OPENJSON() - columns values are transformed into a valid JSON object ('NAVY/44' is translated into '["NAVY", "44"]' for example) and substrings are retrieved using OPENJSON().

Input:

CREATE TABLE #Data (
   [Item] varchar(10),  
   [Variant Descr.] varchar(50), 
   [Variant Order] varchar(50), 
   [Dim_Colour] varchar(50),   
   [Dim_Size] varchar(50),  
   [Dim_Style] varchar(50)
)
INSERT INTO #Data 
   ([Item], [Variant Descr.], [Variant Order], [Dim_Colour], [Dim_Size], [Dim_Style])
VALUES
   ('01', 'NAVY/44',    'COLOUR/SIZE',       NULL, NULL, NULL),
   ('02', 'BLACK/S4',   'COLOUR/STYLE',      NULL, NULL, NULL),
   ('03', 'NAVY/44/S4', 'COLOUR/SIZE/STYLE', NULL, NULL, NULL),
   ('04', 'GREEN',      'COLOUR',            NULL, NULL, NULL)

T-SQL:

-- Dynamic statement
DECLARE @stm nvarchar(max) = N''
SELECT @stm = @stm + 
   N'UPDATE #Data ' +
   N'SET ' + 
   QUOTENAME('Dim_' + j1.[value]) +
   N' = ''' +
   j2.[value] +
   N''' WHERE Item = ''' +
   d.Item +
   N'''; '
FROM #Data d
CROSS APPLY OPENJSON(CONCAT('["', REPLACE([Variant Order], '/', '","'), '"]')) j1 
CROSS APPLY OPENJSON(CONCAT('["', REPLACE([Variant Descr.], '/', '","'), '"]')) j2
WHERE j1.[key] = j2.[key]

-- Execution and output
EXEC (@stm)
SELECT *
FROM #Data

Output:

-----------------------------------------------------------------------------
Item    Variant Descr.  Variant Order     Dim_Colour    Dim_Size    Dim_Style
-----------------------------------------------------------------------------
01      NAVY/44         COLOUR/SIZE       NAVY          44  
02      BLACK/S4        COLOUR/STYLE      BLACK                     S4
03      NAVY/44/S4      COLOUR/SIZE/STYLE NAVY          44          S4
04      GREEN           COLOUR            GREEN

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

And here is a fully generic approach to split this up (thx Zhorov for the MCVE!)

CREATE TABLE #Data (
   [Item] varchar(10),  
   [Variant Descr.] varchar(50), 
   [Variant Order] varchar(50), 
   [Dim_Colour] varchar(50),   
   [Dim_Size] varchar(50),  
   [Dim_Style] varchar(50)
)
INSERT INTO #Data 
   ([Item], [Variant Descr.], [Variant Order], [Dim_Colour], [Dim_Size], [Dim_Style])
VALUES
   ('01', 'NAVY/44',       'COLOUR/SIZE',       NULL, NULL, NULL),
   ('02', 'BLACK/S4',      'COLOUR/STYLE',      NULL, NULL, NULL),
   ('03', 'NAVY/44/S4',    'COLOUR/SIZE/STYLE', NULL, NULL, NULL),
   ('04', 'GREEN/1/2/3/4', 'COLOUR/aNewOne/SIZE/EvenMore/STYLE', NULL, NULL, NULL);
GO

WITH Casted AS
(
    SELECT *
          ,CAST('<x>' + REPLACE(d.[Variant Order],'/','</x><x>') + '</x>' AS XML) AS OrderXml
          ,CAST('<x>' + REPLACE(d.[Variant Descr.],'/','</x><x>') + '</x>' AS XML) AS DescrXml
    FROM #Data d
)
SELECT c.Item
      ,A.Position 
      ,c.OrderXml.value('/x[sql:column("Position")][1]','nvarchar(max)') AS OrderKey
      ,c.DescrXml.value('/x[sql:column("Position")][1]','nvarchar(max)') AS DescrValue
FROM Casted c
CROSS APPLY(SELECT TOP(SELECT c.OrderXml.value('count(/*)','int')) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) FROM master..spt_values) A(Position)

GO

DROP TABLE #Data;

The result

+------+----------+----------+------------+
| Item | Position | OrderKey | DescrValue |
+------+----------+----------+------------+
| 02   | 1        | COLOUR   | BLACK      |
+------+----------+----------+------------+
| 02   | 2        | STYLE    | S4         |
+------+----------+----------+------------+
| 04   | 1        | COLOUR   | GREEN      |
+------+----------+----------+------------+
| 04   | 2        | aNewOne  | 1          |
+------+----------+----------+------------+
| 04   | 3        | SIZE     | 2          |
+------+----------+----------+------------+
| 04   | 4        | EvenMore | 3          |
+------+----------+----------+------------+
| 04   | 5        | STYLE    | 4          |
+------+----------+----------+------------+
| 01   | 1        | COLOUR   | NAVY       |
+------+----------+----------+------------+
| 01   | 2        | SIZE     | 44         |
+------+----------+----------+------------+
| 03   | 1        | COLOUR   | NAVY       |
+------+----------+----------+------------+
| 03   | 2        | SIZE     | 44         |
+------+----------+----------+------------+
| 03   | 3        | STYLE    | S4         |
+------+----------+----------+------------+

You can proceed with conditional aggregation or with a PIVOT approach.

The idea in short:

I use a cast to XML to allow to reach the fragment by its position. Furthermore I use CROSS APPLY together with ROW_NUMBER, which will return a list of numbers depending on the count of the current row's list of fragments. This number is now used within sql:column() to read the fitting fragments side-by-side.

Upvotes: 0

Sreenu131
Sreenu131

Reputation: 2516

Try This

IF OBJECT_ID('tempdb..#temp')IS NOT NULL
DROP TABLE #temp

CREATE TABLE #temp (
            [Item] INT,  
            [Variant Descr.] VARCHAR(100),
            [Variant Order] VARCHAR(100) ,
            [Dim_Colour] VARCHAR(100),
            [Dim_Size]INT ,
            [Dim_Style]  VARCHAR(100)
            )

INSERT INTO  #temp
SELECT 01,'NAVY/44' ,'COLOUR/SIZE' ,NULL,NULL,NULL UNION ALL
SELECT 02,'BLACK/S4','COLOUR/STYLE',NULL,NULL,NULL

SELECT * FROM #temp

UPDATE o 
SET     o.[Dim_Colour]  = dt.Dim_Colour, 
        o.Dim_Size = dt.Dim_Size,
        o.Dim_Style = dt.Dim_Style
FROM #temp o
INNER JOIN
(    

SELECT [Item],  [Variant Descr.], [Variant Order] ,
            SUBSTRING([Variant Descr.],0,CHARINDEX('/',[Variant Descr.])) AS [Dim_Colour]   
           ,CASE WHEN ISNUMERIC(SUBSTRING([Variant Descr.],CHARINDEX('/',[Variant Descr.])+1,LEN ([Variant Descr.]))) = 1 AND [Variant Order] = 'COLOUR/SIZE'   
                  THEN SUBSTRING([Variant Descr.],CHARINDEX('/',[Variant Descr.])+1,LEN ([Variant Descr.]))
                  ELSE NULL END AS [Dim_Size]
           ,CASE WHEN ISNUMERIC(SUBSTRING([Variant Descr.],CHARINDEX('/',[Variant Descr.])+1,LEN ([Variant Descr.]))) <> 1 AND [Variant Order] ='COLOUR/STYLE'
                  THEN SUBSTRING([Variant Descr.],CHARINDEX('/',[Variant Descr.])+1,LEN ([Variant Descr.]))
                  ELSE NULL END AS [Dim_Style]
      FROM  #temp i
)dt ON dt.[Item] = o.[Item]

SELECT * FROM  #temp

Result

Item    Variant Descr.  Variant Order       Dim_Colour  Dim_Size    Dim_Style
------------------------------------------------------------------------------
1       NAVY/44         COLOUR/SIZE            NAVY         44        NULL
2       BLACK/S4        COLOUR/STYLE           BLACK       NULL       S4

Upvotes: 0

Dev
Dev

Reputation: 367

You can get required output by -

select [item] ,
      [variant descr],[variant order],
      left([variant descr],charindex('/', [variant descr]) - 1) AS [Dim_Colour],
      CASE WHEN [variant order] like '%/SIZE' THEN SUBSTRING([variant descr], CHARINDEX('/', [variant descr]) +1, 100)
       ELSE '' END  AS [Dim_Size],
      CASE WHEN [variant order] like '%/STYLE' THEN SUBSTRING([variant descr], CHARINDEX('/', [variant descr]) +1, 100)
       ELSE '' END  AS [Dim_Style]
    from your_table_name

Upvotes: 0

Related Questions