操作包含鍵/值對的列

我正在通過複製的 SQL Server 數據庫訪問和創建來自供應商的報告。他們做了一些我一直在努力解決的絕對瘋狂的事情，但是這件事拿走了蛋糕。

他們有一個包含許多標準列的表。但是這個表也有一個叫做“數據”的列。該列是傳統的“文本”數據類型，它包含一個巨大的（數百個）鍵/值對列表。每對由 CRLF 分隔，鍵和值由等號分隔。例子：

select myTable.[data] from myTable where tblKey = 123

結果：

Key 1=Value 1
Key 2=Value 2
Key 3=Value 3
...
Key 500=Value 500

我正在嘗試確定將該列分解為可用數據表的最有效方法。最終目標是能夠以一種返回表鍵以及指定鍵/值作為列/欄位的方式查詢表，如下所示：

tblKey | [Key 1] | [Key 3] | [Key 243]
-------|---------|---------|-----------
123     Value 1   Value 3   Value 243
124     Value 1   Value 3   Value 243
125     Value 1   Value 3   Value 243

有沒有辦法將該列塑造成一個視圖？我無法想像一個 Function 會特別有效，但我確信我可以使用 string_split 或類似的東西來解析事情。有沒有人遇到過這種暴行並找到了一種將其處理成可用數據的好方法？

編輯以添加dbfiddle範例數據。

數據是從供應商的來源複製的，因此我無法創建新表。我可以創建視圖、過程和函式。這就是我正在尋找一個體面的方式來完成的建議。

更新

如果正如您在自己的答案中發布的那樣，您可以使用 UDF 來獲取特定的鍵值，讓我建議這個：（您不需要拆分所有鍵/值，也不需要再次閱讀表格，您可以通過使用文本函式來獲取它。）

CREATE FUNCTION fnGetKey(@Data text, @Key varchar(20))
RETURNS varchar(100)
AS
BEGIN

 RETURN
 (
 SELECT 
     SUBSTRING (
                 @Data,
                 /* Position of first '=' after key + 1 */
                 CHARINDEX('=', @Data, PATINDEX('%' + @key + '%', @Data)) + 1,
                 /* Lenght, Position of first chr(13) after key less previuos value - 1 */
                 (CHARINDEX(CHAR(13), @Data, PATINDEX('%' + @key + '%', @Data)) 
                 - 
                 CHARINDEX('=', @Data, PATINDEX('%' + @key + '%', @Data))) - 1
               )
 )

END

SELECT
   FruitID, Name, Description,
   dbo.fnGetKey([Data], 'key 2') as [key 2],
   dbo.fnGetKey([Data], 'key 4') as [key 4]
FROM
   [Fruit];

水果ID | 姓名 | 說明 | 鍵 2 | 關鍵 4 
------: | :----- | :---------- | :------ | :------
 1 | 香蕉 | 美味 | 值 2 | 值 4
 2 | 梨 | 羅頓 | 值 2 | 值 4
 3 | 獼猴桃 | 好的 | 值 2 | 值 4

db<>在這裡擺弄

原始答案

我能想到的唯一解決方案是拆分鍵/值，然後旋轉它以獲得所需的結果。

不幸的是，有一些不便之處：

• STRING_SPLIT 不適用於text列。因此，您必須先將其轉換為varchar才能操作它。
• STRING_SPLIT 需要一個nchar(1)or nvarchar(1)，所以你應該用CHAR(3)+CHAR(10)單個字元替換。
• PIVOT 上的聚合函式更適用於數值，那麼您應該Value轉換為某些數值數據類型。
• PIVOT 需要眾所周知的列數，在我的範例中，我使用了其中的一些列，但除非您更願意處理動態查詢，否則您應該編寫整個序列。

這就是我使用您的範例數據得到的結果：

WITH KP AS
(
   SELECT FruitID, Name, Description, value as KPair
   FROM   Fruit
   CROSS APPLY STRING_SPLIT(REPLACE(CAST(Data AS varchar(max)), CHAR(13)+CHAR(10), ','), ',') /* STRING_SPLIT only allows nchar(1),  varchar(1) */
)
, KP1 AS
(
 SELECT
     FruitID,  
     SUBSTRING(KPair, 5, CHARINDEX('=', KPair) - 5) AS [Key],
     SUBSTRING(KPair, CHARINDEX('=', KPair) + 7, LEN(KPair) - CHARINDEX('=', KPair) - 6) AS [Value]
 FROM
     KP
)
SELECT [FruitID], [1],[2],[3],[4],[5]
FROM   KP1
PIVOT (MAX([Value]) FOR [Key] IN ([1],[2],[3],[4],[5])) AS PVT;

第一個 CTE 拆分每個Key X=Value Y. 第二個削減這個值以獲得每個

$$ Key $$和$$ Value $$. 最終的 PIVOT 將最終結果組成列。

水果ID | 1 | 2 | 3 | 4 | 5
------: | :- | :- | :- | :- | :-
 1 | 1 | 2 | 3 | 4 | 5
 2 | 1 | 2 | 3 | 4 | 5
 3 | 1 | 2 | 3 | 4 | 5

db<>在這裡擺弄

注意：我不確定是否應該維護

$$ Key 1 $$&$$ Value 1 $$或者它應該被轉換為一個名為的列$$ Key $$&$$ Value $$. 不同的方法

當我使用第 3 方數據庫時，我通常會在同一伺服器/實例上添加一個新數據庫（如果可能），然後將其用於我自己的目的，以避免與數據庫所有者發生衝突。

在這種情況下，您可以添加一個新表並定期拋出一個程序以使用新值對其進行更新。

您可以使用包含所有列的表：

CREATE TABLE [FruitKeys]
(
   [FruitID] int NOT NULL PRIMARY KEY,
   [V1]      int NULL,
   [V2]      int NULL,
   [V3]      int NULL,
   [V4]      int NULL,
   [V5]      int NULL
);

或帶有鍵/值對的表並使用數據透視表來獲得最終結果：

CREATE TABLE [FruitKeys]
(
   [FruitID] int NOT NULL,
   [Key]     int NOT NULL,
   [Value]   int NOT NULL,
   CONSTRAINT [PK_FruitKeys] PRIMARY KEY ([FruitID], [Key])
);

引用自：https://dba.stackexchange.com/questions/249593