CODEWITHSUNDEEP
sqlsql-server
Harssh Gajjar
Harssh Gajjar

Reputation: 9

Convert comma separated two string values to table

I want to convert comma separated two string values to SQL Server table. Note: Key,value will always contain same length in both strings.

Example:

Key => Dev1,Dev2
Value=> Test1,Test2

Output should be in single table like below:

Column1      Column2
----------   ------
Dev1         Test1
Dev2         Test2

enter image description here

Upvotes: 0

Views: 98

Answers (1)

Zhorov
Zhorov

Reputation: 30023

You need a splitter to parse the input strings into subtrings with their positions. One possible option is a JSON-based approach. You need to transform the comma-separated values into a JSON array and parse this array with OPENJSON() and default schema:

DECLARE @Key varchar(1000) = 'Dev1,Dev2,Dev3'
DECLARE @Value varchar(1000) = 'Test1,Test2,Test3'

SELECT 
   j1.[value] AS Column1,
   j2.[value] AS Column2
FROM OPENJSON(CONCAT('["', REPLACE(STRING_ESCAPE(@Key, 'json'), ',', '","'), '"]')) j1
FULL JOIN OPENJSON(CONCAT('["', REPLACE(STRING_ESCAPE(@Value, 'json'), ',', '","'), '"]')) j2
   ON j1.[key] = j2.[key]

Result:

Column1 Column2
---------------
Dev1    Test1
Dev2    Test2
Dev3    Test3

Upvotes: 1

Related Questions