需要查詢以逗號分隔的列上的表連接

表格1

表 2

結果應如下所示，其中Notescolumn fromTable1應與Namecolumn from連接Table2。

uj5u.com熱心網友回復：

如果您使用 SQL Server 2017 ，您可以嘗試將ReasonID列決議為 JSON，使用適當的JOIN，然后使用STRING_AGG().

樣本資料：

SELECT *
INTO Table1
FROM (VALUES
   (1, 'Test1', '[11,12]'),
   (2, 'Test2', '[13,14]')
) t (ID, Notes, ReasonID)

SELECT *
INTO Table2
FROM (VALUES
   (11, 'Other1'),
   (12, 'Other2'),
   (13, 'Other3'),
   (14, 'Other4')
) t (ReasonID, Name)

陳述：

SELECT 
   ID,
   FinalNotes = CONCAT(
      Notes,
      ',',
      (
      SELECT STRING_AGG(t2.Name, ',') WITHIN GROUP (ORDER BY CONVERT(int, j.[key]))
      FROM OPENJSON(ReasonID) j
      -- Important, JOIN with possible implicit conversion
      JOIN Table2 t2 ON j.[value] = t2.ReasonID
      )
   )
FROM Table1

結果：

db<>小提琴

uj5u.com熱心網友回復：

請嘗試以下解決方案。

它將從 SQL Server 2012 開始作業。

它使用以下內容：

• XML/XQuery 用于標記逗號分隔的值串列。
• FOR XML PATH組成FinalNotes逗號分隔串列。

SQL

-- DDL and sample data population, start
DECLARE @Table1 TABLE(ID INT, Notes VARCHAR(60), ReasonID VARCHAR(60));
INSERT INTO @Table1(ID, Notes, ReasonID) VALUES 
(1, 'Test1', '[11,12]'), 
(2, 'Test2', '[13,14]');

DECLARE @Table2 TABLE(Reasonid INT, Name VARCHAR(60));
INSERT INTO @Table2(Reasonid, Name) VALUES 
(11, 'Other1'), 
(12, 'Other2'), 
(13, 'Other3'), 
(14, 'Other4');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ',';

;WITH rs AS
(
    SELECT ID, Notes, Name
    FROM @Table1 AS t
    CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA['   
        REPLACE(REPLACE(REPLACE(ReasonID,'[',''),']',''), @separator, ']]></r><r><![CDATA[')   
        ']]></r></root>' AS XML)) AS t1(c)
    CROSS APPLY c.nodes('/root/r/text()') AS t2(x)
        INNER JOIN @Table2 AS t3 ON t3.Reasonid = x.value('.', 'INT')
)
SELECT ID, CONCAT(Notes
    , (SELECT @separator   c.Name AS [text()]
       FROM rs AS c
       WHERE c.ID = p.ID
       FOR XML PATH(''))) AS FinalNotes
FROM rs AS p
GROUP BY ID, Notes;

輸出

 ---- --------------------- 
| ID |     FinalNotes      |
 ---- --------------------- 
|  1 | Test1,Other1,Other2 |
|  2 | Test2,Other3,Other4 |
 ---- --------------------- 

uj5u.com熱心網友回復：

用于SUBSTRING(string, 2, LEN(string)-2) 洗掉[] 和Parsename基于逗號分割join和concat如下

您的資料

DECLARE @Table1 TABLE(
  ID INTEGER NOT NULL, 
  Notes VARCHAR(60) NOT NULL, 
  ReasonID VARCHAR(60) NOT NULL
);
INSERT INTO @Table1(ID, Notes, ReasonID) 
VALUES 
  (1, 'Test1', '[11,12]'), 
  (2, 'Test2', '[13,14]');

DECLARE @Table2 TABLE(
  Reasonid INTEGER NOT NULL, 
  Name VARCHAR(60) NOT NULL
);
INSERT INTO @Table2(Reasonid, Name) 
VALUES 
  (11, 'Other1'), 
  (12, 'Other2'), 
  (13, 'Other3'), 
  (14, 'Other4');

您的查詢

SELECT id,
       Concat(notes, ',', T2.name, ',', T3.name) FinalNotes
FROM  (SELECT id,
              notes,
              Parsename(Replace(SUBSTRING(ReasonID, 2, LEN(ReasonID)-2), ',', '.'), 2) R1,
              Parsename(Replace(SUBSTRING(ReasonID, 2, LEN(ReasonID)-2), ',', '.'), 1) R2
       FROM   @table1) T1
      join @table2 T2
        ON T1.R1 = T2.reasonid
      join @table2 T3
        ON T1.R2 = T3.reasonid  

通過使用 XML

 DROP TABLE IF EXISTS #t -- temporary table
  select t1.ID,t1.Notes, Name into #t -- temporary table
  from 
(
SELECT A.ID,a.Notes,  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT ID, Notes, 
         CAST ('<M>'   REPLACE(SUBSTRING(ReasonID, 2, LEN(ReasonID)-2)  , ',', '</M><M>')   '</M>' AS XML) AS String  
     FROM  @Table1) AS A CROSS APPLY String.nodes ('/M') AS Split(a)) t1 
     join @Table2 t2 on t1.String=t2.Reasonid
---XML Path
SELECT  ID,concat(notes,',',
       STUFF((SELECT ', '   CAST(name AS VARCHAR(10)) [text()]
         FROM #t t1
         WHERE t1.ID = t.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ')) FinalNotes
FROM #t t
GROUP BY ID,notes

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

上一篇：在sqlserver中將二進制資料存盤為nvarchar/varchar資料

下一篇：檢索最暢銷的產品