SQL-根據分隔符將合并欄位分成單獨的欄位

我有一個包含 nvarchar(max) 列的表，其中包含如下合并文本：

ID  MyString
61  Team:Finance,Accounting,HR,Country:Global,
62  Country:Germany,
63  Team:Legal,
64  Team:Finance,Accounting,Country:Global,External:Tenants,Partners,
65  External:Vendors,

我需要為每個專案創建另一個表，將 Team、Country 和 External 值分成 3 個不同的列。

Id   Team                      Country   External
61   Finance,Accounting,HR     Global    NULL
62   NULL                      Germany   NULL
63   Legal                     NULL      NULL
64   Finance,Accounting        Global    Tenants,Partners
65   NULL                      NULL      Vendors

最有效的方法是什么？我正在嘗試使用 STRING_SPLIT 但無法管理它。

任何幫助，將不勝感激。

uj5u.com熱心網友回復：

請嘗試以下解決方案。

資料類似于 JSON，因此我們將通過幾個REPLACE()函式呼叫組成一個正確的 JSON。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT PRIMARY KEY, tokens NVARCHAR(MAX));
INSERT INTO @tbl (ID, tokens) VALUES
(61, 'Team:Finance,Accounting,HR,Country:Global,'),
(62, 'Country:Germany,'),
(63, 'Team:Legal,'),
(64, 'Team:Finance,Accounting,Country:Global,External:Tenants,Partners,'),
(65, 'External:Vendors,');
-- DDL and sample data population, end

SELECT * 
FROM @tbl
CROSS APPLY OPENJSON('{"'   REPLACE(REPLACE(REPLACE(TRIM(',' FROM tokens), ':', '": "')
    ,',Country',  '", "Country')
    ,',External', '", "External')   '"}')
WITH
(
   Team         VARCHAR(100)    '$.Team',
   Country      VARCHAR(100)    '$.Country',
   [External]   VARCHAR(100)    '$.External'
) AS u;

輸出

 ---- ------------------------------------------------------------------- ----------------------- --------- ------------------ 
| ID |                              tokens                               |         Team          | Country |     External     |
 ---- ------------------------------------------------------------------- ----------------------- --------- ------------------ 
| 61 | Team:Finance,Accounting,HR,Country:Global,                        | Finance,Accounting,HR | Global  | NULL             |
| 62 | Country:Germany,                                                  | NULL                  | Germany | NULL             |
| 63 | Team:Legal,                                                       | Legal                 | NULL    | NULL             |
| 64 | Team:Finance,Accounting,Country:Global,External:Tenants,Partners, | Finance,Accounting    | Global  | Tenants,Partners |
| 65 | External:Vendors,                                                 | NULL                  | NULL    | Vendors          |
 ---- ------------------------------------------------------------------- ----------------------- --------- ------------------ 

uj5u.com熱心網友回復：

首先，讓我在這里重復我的評論。SQL Server 是你應該做的最后一個地方；它的字串操作很差，你有一個嚴重的非規范化設計，非規范化資料包含非規范化資料。將您的設計修復為規范化方法必須是當務之急，因為讓您的資料處于這種狀態只會讓您越深入這個兔子洞，事情就越難。

但是，您可以用來實作此目的的一種方法是使用 JSON 拆分器和一些重新字串聚合，但這真的很難看。將“列”和“行”定界符都設為逗號 ( ,) 的選擇使這變得一團糟，我不打算解釋它在做什么，因為你不應該這樣做。

WITH YourTable AS(
    SELECT *
    FROM (VALUES(61,'Team:Finance,Accounting,HR,Country:Global,'),
                (62,'Country:Germany,'),
                (63,'Team:Legal,'),
                (64,'Team:Finance,Accounting,Country:Global,External:Tenants,Partners,'),
                (65,'External:Vendors,'))V(ID,MyString)),
PartiallyNormal AS(
    SELECT YT.ID,
           CONVERT(int,LEAD(OJC.[Key],1,OJC.[Key]) OVER (PARTITION BY ID ORDER BY OJC.[Key], OJV.[Key])) AS ColumnNo,
           OJV.[value],
           CONVERT(int,OJC.[key]) AS [key]
    FROM YourTable YT
         CROSS APPLY OPENJSON(CONCAT('["', REPLACE(YT.MyString,':','","'),'"]')) OJC
         CROSS APPLY OPENJSON(CONCAT('["', REPLACE(OJC.[value],',','","'),'"]')) OJV),
WithNames AS(
    SELECT ID,
           ColumnNo,
           [value],
           [key],
           FIRST_VALUE(PN.[Value]) OVER (PARTITION BY ID, ColumnNo ORDER BY [Key]) AS ColumnName
    FROM PartiallyNormal PN)
SELECT ID,
       TRIM(',' FROM STRING_AGG(CASE ColumnName WHEN 'Team' THEN NULLIF([value],'''') END,',') WITHIN GROUP (ORDER BY [key])) AS Team, --TRIM because I've not taken the time to work out why there are sometimes a trailing comma
       TRIM(',' FROM STRING_AGG(CASE ColumnName WHEN 'Country' THEN NULLIF([value],'''') END,',') WITHIN GROUP (ORDER BY [key])) AS Country,
       TRIM(',' FROM STRING_AGG(CASE ColumnName WHEN 'External' THEN NULLIF([value],'''') END,',') WITHIN GROUP (ORDER BY [key])) AS [External]
FROM WithNames WN
WHERE [value] <> [ColumnName]
GROUP BY ID
ORDER BY ID;

db<>小提琴

uj5u.com熱心網友回復：

STRING_SPLIT在 SQL Server 2017 中并沒有告訴我們串列中專案的順序，所以這里不能使用。只有 SQL Server 2022 會添加一個引數來STRING_SPLIT告訴專案的順序。

在那個版本的 SQL Server 之前，最有效的方法可能是 CLR。用 C# 撰寫決議器并使用 CLR 呼叫函式。

uj5u.com熱心網友回復：

另一種選擇是：

• STRING_SPLIT使用冒號上的函式拆分字串
• LAG使用函式提取連續字串
• 洗掉字串識別符號 ( Team,Country和External)
• 聚合ID以洗掉 NULL 值

這是查詢：

WITH cte AS (
    SELECT ID,
           LAG(value) OVER(PARTITION BY ID ORDER BY (SELECT 1)) AS prev_value,
           value
    FROM tab
    CROSS APPLY STRING_SPLIT(MyString, ':')
)
SELECT ID,
       MAX(CASE WHEN prev_value LIKE 'Team' 
                THEN REPLACE(value, ',Country', '') END) AS [Team],
       MAX(CASE WHEN prev_value LIKE '%Country' 
                THEN LEFT(value, LEN(value)-1)      END) AS [Country],
       MAX(CASE WHEN prev_value LIKE '%External' 
                THEN LEFT(value, LEN(value)-1)      END) AS [External]
FROM cte
GROUP BY ID

在此處查看演示。

標籤：sql sql服务器 sql-server-2017

上一篇：使用“Trustservercertificate=true”配置log4net連接字串AdoNetAppender

下一篇：SQL查詢根據另一列中的最大值更新記錄