none
split a parmeter

    Question

  • Hi,

    Table structure is below that stores answers to test

    Student_Test table

    Student_Id         Question_Id     ValueCode

    1                             10                           100

    2                              20                           102

    Question table

    Question_Id                      Question

    10                                         Question1

    20                                         Question2

    Question_Value_Code table

    Question_Id              Value_Code_Id                                  Description                 Value

    10                                           100                                         Yes                         1

    10                                           101                                         No                          2

    20                                           102                                         Yes                         1

    20                                           103                                         No                          2

    20                                           104                                       Don’t know                3

    Now I am trying to find students who answered particular question yes like that

    I am getting value code ids in parameter @valuecodeId. How to split @valuecodeid parameter by Question_Id and store each question values in separate variable which in turn apply against Value_Code_Id table to find students.

    @ValuecodeId = “100,101,103” then split @question1 = “100, 101” and @question2 = “103” 

    I know how to split into multiple @queston variables but wondering is there a way to do this with out splitting into multiple variables and directly doing IN parameter in below query.

    Declare @value_code_id varchar(200)
    set @value_code_id = '100,101,102'

    -- split above param into @question1 and @question2 and create questionX variables only if value present in @value_code_id parameter
    select student_id, s.value_code_id, qv.description
    from student_test s
    inner join question q on s.question_id = q.question_id
    inner join Question_Value_Code qv on q.question_id = qv.question_id
    and ( qv.value_code_id in (@question1) and qv.value_code_id in (@question2))

    Monday, March 20, 2017 8:25 PM

All replies

  • DDL:

    create table student_test(student_id int, Question_Id int, Value_Code int)

    insert into student_test values(1,10,100)
    insert into student_test values(2,20,102)

    create table Question(Question_Id int, Question varchar(200))
    insert into Question values(10, 'Question 1')
    insert into Question values(20, 'Question 2')

    create table Question_Value_Code (Question_Id int, value_code_Id int, description varchar(20), value int)
    insert into Question_Value_Code values(10, 100, 'Yes', 1)
    insert into Question_Value_Code values(10, 101, 'No', 2)
    insert into Question_Value_Code values(20, 102, 'Yes', 1)
    insert into Question_Value_Code values(20, 103, 'No', 2)
    insert into Question_Value_Code values(20, 104, 'Don''t know', 3)

    Monday, March 20, 2017 8:26 PM
  • I am looking for with out splitting if I have 100 questions don't want to declare that many variables. In future if no question gets added, don't want to change proc.
    Monday, March 20, 2017 8:28 PM
  • The problem seems to be that you are thinking in comma-separate lists and variables when you should be thiniking in tables.

    You split this valuecodeid-string and join that to the table which maps to the questions.

    I don't really understand the logic involved, but
      in (@question1)

    Should be something like IN (SELECT code FROM @sometable WHERE ...)

    Monday, March 20, 2017 11:09 PM
  • From @Value_Code_Id parameter, I want to take all values of question1 (if any present in @value_code_id param) and store in @question1 parameter like "100, 101" and do same for remaining questions like @question2 etc. At the end apply these individual parameters with AND

    select student_id, s.value_code_id, qv.description
    from student_test s
    inner join question q on s.question_id = q.question_id
    inner join Question_Value_Code qv on q.question_id = qv.question_id
    and ( qv.value_code_id in (@question1) and qv.value_code_id in (@question2))

    my question is, suppose if I have 100s of questions, don't want to split and declare that many variable, so wondering is there any way of doing this efficiently?

    TIA

    Tuesday, March 21, 2017 1:43 AM
  • Hi bluepink,

    It is necessary to split the csv. You may check if the following script helps.

    ;WITH CTE AS
    (
    	SELECT [value] FROM STRING_SPLIT('100,102', ',') -- SQL 2016
    )
    SELECT s.student_id
    FROM student_test s
    JOIN CTE t ON s.Value_Code = t.[value]
    GROUP BY s.student_id
    HAVING COUNT(DISTINCT s.Value_Code) = (SELECT COUNT(1) FROM CTE)

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 21, 2017 4:31 AM
    Moderator
  • From @Value_Code_Id parameter, I want to take all values of question1 (if any present in @value_code_id param) and store in @question1 parameter like "100, 101" and do same for remaining questions like @question2 etc. At the end apply these individual parameters with AND

    No, you don't! Stop thinking in variables! You should split the string into a table, and the map the ids to the questions.

    The query you posted does not make sense:

    and ( qv.value_code_id in (@question1) and qv.value_code_id in (@question2))

    qv.value_code_id cannot be both equal to @question1 and @question2 at the same time (unless the variables have the same value.

    Tuesday, March 21, 2017 8:14 AM
  • Yes it's possible, there can be other students whose answers might fall into @question2 values and some students answers fall into @question1, here I am trying to find out all the students.

    Now When @Value_Code_Id = "100, 102"

    then @question1 = 100 @question2 = "102" the query should return students with Id 1 and 2.

    I am looking to for efficient way of doing this.

    Tuesday, March 21, 2017 2:06 PM
  • It is difficult to understand your tables. Partly this may be because you have not indicated the keys. For the data you want to return, I don't see why you would drag in the Questions table at all.

    The code below is for SQL 2016. See my article for alternatives to string_split if you are an earlier version:
    http://www.sommarskog.se/arrays-in-sql.html

    create table student_test(student_id int, Question_Id int, Value_Code int)

    insert into student_test values(1,10,100)
    insert into student_test values(2,20,102)

    create table Question(Question_Id int, Question varchar(200))
    insert into Question values(10, 'Question 1')
    insert into Question values(20, 'Question 2')

    create table Question_Value_Code (Question_Id int, value_code_Id int, description varchar(20), value int)
    insert into Question_Value_Code values(10, 100, 'Yes', 1)
    insert into Question_Value_Code values(10, 101, 'No', 2)
    insert into Question_Value_Code values(20, 102, 'Yes', 1)
    insert into Question_Value_Code values(20, 103, 'No', 2)
    insert into Question_Value_Code values(20, 104, 'Don''t know', 3)

    Declare @value_code_id varchar(200)
    set @value_code_id = '100,101,102'

    select student_id, s.Value_Code, qv.description
    from student_test s
    join   Question_Value_Code qv ON s.Value_Code = qv.value_code_Id
    WHERE  s.Value_Code IN (SELECT value FROM string_split(@value_code_id, ','))

    go
    DROP TABLE student_test, Question, Question_Value_Code

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, March 21, 2017 11:14 PM

  • WHERE  s.Value_Code IN (SELECT value FROM string_split(@value_code_id, ','))

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    I know how to split csv string, I am looking for while splitting values from CSV string, split them by question Id and apply (into a group) apply AND between each group and with in group apply IN.

    I can split by group also, store each group of values into variables. I am looking for is there a way to do this instead of declare n number of variables where n is number of questions in database.

    Thanks

    Wednesday, March 22, 2017 2:02 PM
  • I know how to split csv string, I am looking for while splitting values from CSV string, split them*by question Id* and apply (into a group) apply AND between each group and with in group apply IN.

    But why on earth would you do that!

    Split your value_code_string into a table, and map it to questions.

    Then why you want to do this IN thing, I have not understood. All I have seen is a query that never will return any results.

    What do you want to achieve really? What result do you want with the test data you posted?

    Wednesday, March 22, 2017 11:11 PM
  • I think you don't get what I am trying to do here: let me say again:

    When @value_Code_Id param has "100, 102", I would like to return students 1 ans 2 rows since student 1 answered question1 as Yes and student2 answered question2 also yes.

     I want to find students who answered given possible question answers. Given answers are in @value_code_id parameter. Lets think answers group by question Id, If the answers fall in one group then select all students who have answered either of the values in that group. Apply AND between question groups.

    Hope this helps.
    Thursday, March 23, 2017 1:05 AM
  • I think you don't get what I am trying to do here: let me say again:

    That is quite a correct observation, yes. I'm not sure that I still do.

    When @value_Code_Id param has "100, 102", I would like to return students 1 ans 2 rows since student 1 answered question1 as Yes and student2 answered question2 also yes.

    I believe that my query did that?

     I want to find students who answered given possible question answers. Given answers are in @value_code_id parameter. Lets think answers group by question Id, If the answers fall in one group then select all students who have answered either of the values in that group. Apply AND between question groups.Hope this helps.

    It could be that my native language is not English, but I still not get what you are trying to say. Could you provide more sample data with more students and questions. And most importantly, students that should not be returned.

    Thursday, March 23, 2017 11:08 PM