CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
Surensiveaya
Surensiveaya

Reputation: 347

Loop and insert more than one comma separated List in SQL

I wish to loop through two comma-separated values and perform an insert

As an example lets consider two variables

Declare   @Qid= 1,4,6,7,8   @Answers = 4,4,3,2,3

set @pos = 0
set @len = 0

WHILE CHARINDEX(',', @Answers, @pos+1)>0
BEGIN
    set @len = CHARINDEX(',', @Answers, @pos+1) - @pos
    set @value = SUBSTRING(@Answers, @pos, @len)

insert into table values(@fdid,@Qid,@fusid, @value)  -- i need Qid also

set @pos = CHARINDEX(',', @Answers, @pos+@len) +1
END

Using this loop I am able to extract @Answers and can perform insert. But I wish to extract @Qid and insert inside the loop.

edit for more clarity it is a feedback module. my result table have Qid and Answer field. Answers are ratings (1 to 5). The values we get in variables @Qid and @Answers are sequential. which means 1st answer will be for 1st question and so on.

edit

as per Shnugo's Answer

Declare @Qid varchar(100)= '1,4,6,7,8',   @Answers varchar(100)= '4,4,3,2,3'
DECLARE @tbl TABLE(ID INT IDENTITY, Questions VARCHAR(100),Answers VARCHAR(100));
INSERT INTO @tbl VALUES(@Qid,@Answers)


 INSERT INTO table(FeedbackId,QuestionId,FeedbackUserId,Answer)
 SELECT  1,
  A.qXml.value('(/x[sql:column("B.QuestionCount")])[1]','int') AS QuestionNumber,3
      ,A.aXml.value('(/x[sql:column("B.QuestionCount")])[1]','int') AS AnwerNumber
FROM @tbl t
CROSS APPLY(SELECT CAST('<x>' + REPLACE(@Qid,',','</x><x>') + '</x>' AS XML)
                  ,CAST('<x>' + REPLACE(@Answers,',','</x><x>') + '</x>' AS XML)) A(qXml,aXml)
CROSS APPLY(SELECT TOP(A.qXml.value('count(/x)','int')) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values) B(QuestionCount)

Upvotes: 0

Views: 1349

Answers (3)

Zhorov
Zhorov

Reputation: 29963

If you use SQL Server 2016 or higher, you may try to use the next JSON-based approach to map questions and answers by their positions in the input strings. You need to transform the input strings into valid JSON arrays and then use OPENJSON() with default schema to parse the arrays. The result is a table, with columns key, value and type and the key column holds the index of the element in the specified array.

Note, that STRING_SPLIT() function does not guarantee the order of the rows and the output rows might be in any order.

Statement:

DECLARE @Qid nvarchar(max) = N'1,4,6,7,8'
DECLARE @Answers nvarchar(max) = N'4,4,3,2,3'

-- Build your INSERT statement as you expect
-- INSERT INTO Table ...
SELECT j1.[value] AS Qid, j2.[value] AS Answers  
FROM OPENJSON(CONCAT(N'[', @Qid, N']')) j1
JOIN OPENJSON(CONCAT(N'[', @Answers, N']')) j2 ON j1.[key] = j2.[key]

Result from the SELECT statement:

Qid Answers
1   4
4   4
6   3
7   2
8   3

Upvotes: 3

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

I'd prefer Zhorov's JSON answer (needs v2016+).

If you use a SQL-Server below 2016 you might use this position-safe XML-based solution:

A mockup table to simulate your issue with two different rows.

DECLARE @tbl TABLE(ID INT IDENTITY, Questions VARCHAR(100),Answers VARCHAR(100));
INSERT INTO @tbl VALUES('1,4,6,7,8','4,4,3,2,3')
                      ,('1,2,3','4,5,6');

--The query

SELECT t.*
      ,A.qXml.value('(/x[sql:column("B.QuestionCount")])[1]','int') AS QuestionNumber
      ,A.aXml.value('(/x[sql:column("B.QuestionCount")])[1]','int') AS AnwerNumber
FROM @tbl t
CROSS APPLY(SELECT CAST('<x>' + REPLACE(t.Questions,',','</x><x>') + '</x>' AS XML)
                  ,CAST('<x>' + REPLACE(t.Answers,',','</x><x>') + '</x>' AS XML)) A(qXml,aXml)
CROSS APPLY(SELECT TOP(A.qXml.value('count(/x)','int')) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values) B(QuestionCount);

The idea in short:

We need a CROSS APPLY and some string methods to transform something like 1,2,3 to an xml like <x>1</x><x>2</x><x>3</x>.
Now we can use value() with XQuery count() to find the actual count of questions.
We need one more CROSS APPLY with a computed TOP() clause to get a set of running number from 1 to n with n=countOfQuestions. I do this against master..spt_values. This is just a well-filled standard table... We do not need the values, just any set to create the counter...
Finally we can use .value() in connection with sql:column() in order to fetch the question and the corresponding answer by their positions.

UPDATE: Non-tabular data

If you do not get these CSV parameters as a table you can use this:

Declare @Qid varchar(100)= '1,4,6,7,8',   @Answers varchar(100)= '4,4,3,2,3'

--INSERT INTO table(FeedbackId,QuestionId,FeedbackUserId,Answer)
SELECT  1
       ,A.qXml.value('(/x[sql:column("B.QuestionCount")])[1]','int') AS QuestionNumber
       ,3
       ,A.aXml.value('(/x[sql:column("B.QuestionCount")])[1]','int') AS AnwerNumber
FROM (SELECT CAST('<x>' + REPLACE(@Qid,',','</x><x>') + '</x>' AS XML)
            ,CAST('<x>' + REPLACE(@Answers,',','</x><x>') + '</x>' AS XML)) A(qXml,aXml)
CROSS APPLY(SELECT TOP(A.qXml.value('count(/x)','int')) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values) B(QuestionCount);

Upvotes: 3

Ajay2707
Ajay2707

Reputation: 5798

You have not described relationship of question and its answers. I feel its one to one relationship and for that, I have given the answer.

declare @Qid varchar(200)= '1,4,6,7,8' , @Answers varchar(200) = '4,4,3,2,3'

;with cte
as(
    select id, data qid from dbo.Split (@qid, ',') 
),
cte1 as
(
select id, data ansid from dbo.Split (@answers, ',') 
)


--insert into tablename 
select
qid, ansid from cte join cte1 on cte.id = cte1.id

Result will be:

qid ansid
1    4
4    4
6    3
7    2
8    3

See other option for later version of sqlserver : Split function equivalent in T-SQL?

Upvotes: 0

Related Questions