假設我們有以下兩個字串：
DECLARE @AllowedCardBoardIds NVARCHAR(MAX) = '1,2,3,4,5,6'
DECLARE @AllowedCardBoardIdsAccessTypes NVARCHAR(MAX) = '11,22,33,44,55,66'
另外，假設我們有一個將字串拆分為表格的函式。
它回傳一個序列值
Select * FROM dbo.SplitString(',', @AllowedCardBoardIds)
-- result:
Value
------
1
2
3
4
5
6
現在我想將兩個字串轉換為表格并將它們合并如下：
Id | AccessType
--------------------
1 | 11
2 | 22
3 | 33
4 | 44
5 | 55
6 | 66
我該怎么做？
我寫了一個查詢如下：
DECLARE @AllowedCardBoardsTable TABLE(
Id INT NOT NULL,
AccessType INT NOT NULL
)
INSERT INTO @AllowedCardBoardsTable
(
Id, AccessType
)
SELECT id.[Value], accessType.[Value]
FROM dbo.SplitString(',', @AllowedCardBoardIds) AS id
但我不知道如何填寫AccessType專欄！
uj5u.com熱心網友回復：
請嘗試以下解決方案。
SQL
DECLARE @AllowedCardBoardsTable TABLE(
Id INT NOT NULL,
AccessType INT NOT NULL
);
DECLARE @AllowedCardBoardIds NVARCHAR(MAX) = '1,2,3,4,5,6';
DECLARE @AllowedCardBoardIdsAccessTypes NVARCHAR(MAX) = '11,22,33,44,55,66';
DECLARE @separator CHAR(1) = ',';
;WITH rs AS
(
SELECT CardBoardIds = CAST('<root><r><![CDATA['
REPLACE(@AllowedCardBoardIds, @separator, ']]></r><r><![CDATA[')
']]></r></root>' AS XML)
, CardBoardIdsAccessTypes = CAST('<root><r><![CDATA['
REPLACE(@AllowedCardBoardIdsAccessTypes, @separator, ']]></r><r><![CDATA[')
']]></r></root>' AS XML)
)
, rs2 AS
(
SELECT rn = ROW_NUMBER() OVER(ORDER BY (t.c))
, Id = c.value('.', 'INT')
FROM rs
CROSS APPLY CardBoardIds.nodes('/root/r/text()') AS t(c)
)
, rs3 AS
(
SELECT rn = ROW_NUMBER() OVER(ORDER BY (t.c))
, Id = c.value('.', 'INT')
FROM rs
CROSS APPLY CardBoardIdsAccessTypes.nodes('/root/r/text()') AS t(c)
)
INSERT INTO @AllowedCardBoardsTable (Id, AccessType)
SELECT rs2.id, rs3.Id
FROM rs2 INNER JOIN rs3 ON rs3.rn = rs2.rn;
-- test
SELECT * FROM @AllowedCardBoardsTable;
輸出
---- ------------
| Id | AccessType |
---- ------------
| 1 | 11 |
| 2 | 22 |
| 3 | 33 |
| 4 | 44 |
| 5 | 55 |
| 6 | 66 |
---- ------------
uj5u.com熱心網友回復：
我建議您使用一種內置的字串拆分方法，而不是嘗試自己動手。
不幸的是，當前版本的 SQL Server 不支持STRING_SPLIT列ordinal。但在這種情況下，你可以破解它OPENJSON
DECLARE @AllowedCardBoardIds NVARCHAR(MAX) = '1,2,3,4,5,6';
DECLARE @AllowedCardBoardIdsAccessTypes NVARCHAR(MAX) = '11,22,33,44,55,66';
SELECT
id = acb.value,
AccessType = at.value
FROM OPENJSON('[' @AllowedCardBoardIds ']') acb
JOIN OPENJSON('[' @AllowedCardBoardIdsAccessTypes ']') at
ON at.[key] = acb.[key];
db<>小提琴
我強烈建議您首先將資料正確規范化，例如存盤在表變數、臨時表或普通表中。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/467837.html
標籤：sql sql服务器 sql-server-2008-r2
上一篇：將更改的員工和新員工追加到現有表
下一篇：當前 前XN行的總和