将字符串分开，然后将零件插入表格中的正确列中

Question

我正在尝试使用SQL Server构建事实表。目前看起来像这样：

[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

我需要将字符串分开[variant descr。]，然后将零件插入正确的dim_列中，以最终将看起来像这样：

[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

问题是[变体顺序]的部分和[变体descr。]的部分可能会有所不同。因此，我基本上需要做类似的事情：

1. 在[variant descr。]
中获取字符串
2. 在[变体顺序]中获取字符串
3. 将第一个值插入由第二个值指定的列
4. 对[variant descr]的所有部分执行此操作
5. 对表中的每一行执行此操作

我已经尝试使用用户定义的函数来解决此问题，只是发现我无法在UDF中使用动态SQL。

任何帮助将不胜感激

欢呼！

Answer 1

一种可能的方法是将[variant descr。]和[variant Order]列中的文本拆分，并使用动态语句更新表。尽管使用STRING_SPLIT()是从SQL Server 2016开始的首选，但在这种情况下，此功能不是一个选项，因为不能保证子字符串的顺序。一个工作解决方案是使用OPENJSON()-列值转换为有效的JSON对象(例如，将'NAVY/44'转换为'["NAVY", "44"]'(，并使用OPENJSON()。

输入：

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

输出：

-----------------------------------------------------------------------------
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     

Answer 2

您可以通过 -

获得所需的输出

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

Answer 3

尝试此

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 

结果

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

Answer 4

，这是一种将其拆分的完全通用方法(thx zhorov for 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;

结果

+------+----------+----------+------------+
| 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         |
+------+----------+----------+------------+

您可以进行条件聚合或PIVOT方法。

简而言之：

我使用将铸件铸造到XML，以允许其位置到达片段。此外，我将CROSS APPLY与ROW_NUMBER一起使用，它将根据当前行的片段列表的计数返回数字列表。现在，该数字在sql:column()中用于读取拟合片段并排。