Joining sql tables with no common columns without ordering-CodePudding

I have my data in a form of 2 coma separated strings

DECLARE @ids nvarchar(max) = '1,2,3'
DECLARE @guids nvarchar(max) = 
'0000000-0001-0000-0000-000000000000,
`0000000-0022-0000-0000-000000000000`,
`0000000-0013-0000-0000-000000000000'`

I need them in a table as separate columns based on their position in the string

Table1
| Id | Guid                                |
| 1  | 0000000-0001-0000-0000-000000000000 |
| 2  | 0000000-0022-0000-0000-000000000000 |
| 3  | 0000000-0013-0000-0000-000000000000 |

I can split both strings into separate tables by using

DECLARE @split_ids
(value nvarchar(max))

DECLARE @xml xml
SET @xml = N'<root><r>'   replace(@ids, ',' ,'</r><r>')   '</r></root>'

INSERT INTO @split_ids(Value)
SELECT r.value('.','nvarchar(max)')
FROM @xml.nodes('//root/r') as records(r)

I've tried

 SELECT t1.*, t2.*
 FROM (SELECT t1.*, row_number() OVER (ORDER BY [Value]) as seqnum
  from cte_Ids t1
 ) t1 FULL OUTER  JOIN
 (SELECT t2.*, row_number() OVER (ORDER BY [Value]) as seqnum
  from cte_barcodes t2
 ) t2
 ON t1.seqnum = t2.seqnum;

But that orders the tables by Value and my data is random and can't be ordered.

Is there a way of joining tables based on their row numbers without ordering them first?

Or is there another way of inserting data from a string to a table?

CodePudding user response：

One of solutions is to parse your comma separated values in a loop (using WHILE) from both variables. Then you could insert those extracted in the same iteration values at once as one row to a table.

CodePudding user response：

An XML-based approach or a splitter function are possible solutions. But if you use SQL Server 2016 , a JSON-based approach is also an option. The idea is to transform the strings into valid JSON arrays (1,2,3 into [1,2,3]), parse the arrays with OPENJSON() and join the tables returned from OPENJSON() calls. As is explained in the documentation, the columns that OPENJSON() function returns (when the default schema is used) are key, value and type and in case of JSON array, the key column holds the index of the element in the specified array.

DECLARE @ids nvarchar(max) = N'1,2,3'
DECLARE @guids nvarchar(max) =  N'0000000-0001-0000-0000-000000000000,0000000-0022-0000-0000-000000000000,0000000-0013-0000-0000-000000000000'

SELECT j1.[value] AS Id, j2.[value] AS Guid
FROM OPENJSON(CONCAT('[', @ids, ']')) j1
JOIN OPENJSON(CONCAT('["', REPLACE(@guids, ',', '","'), '"]')) j2 ON j1.[key] = j2.[key]

Result:

Id  Guid
1   0000000-0001-0000-0000-000000000000
2   0000000-0022-0000-0000-000000000000
3   0000000-0013-0000-0000-000000000000