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)

Answer

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);