Assume we have two below strings:

DECLARE @AllowedCardBoardIds NVARCHAR(MAX) = '1,2,3,4,5,6'
DECLARE @AllowedCardBoardIdsAccessTypes NVARCHAR(MAX) = '11,22,33,44,55,66'

Also, assume we have a function to split a string as a table.
It returns a sequence values

Select * FROM dbo.SplitString(',', @AllowedCardBoardIds)
-- result:
Value
------
1
2
3
4
5
6

Now I want to convert two string to table and merge them as follows:

Id     | AccessType
--------------------
1      | 11
2      | 22
3      | 33
4      | 44
5      | 55
6      | 66

How should I do it?

I wrote a query as follows:

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

But I don't know how to fill AccessType column !

CodePudding user response：

Please try the following solution.

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;

Output

 ---- ------------ 
| Id | AccessType |
 ---- ------------ 
|  1 |         11 |
|  2 |         22 |
|  3 |         33 |
|  4 |         44 |
|  5 |         55 |
|  6 |         66 |
 ---- ------------ 

CodePudding user response：

I would suggest you use one of the built-in string-splitting methods, rather than trying to roll your own.

Unfortunately, current versions of SQL Server do not support STRING_SPLIT with an ordinal column. But in this case you can hack it with 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<>fiddle

I strongly suggest you store your data properly normalized in the first place, such as in a table variable, temp table, or normal table.