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
  • I've updated my sample data:

    Create table Question(Question_Id int, Question varchar(200))
    create table Question_Value_Code(Question_Id int, Value_Code_Id int, Description varchar(100))
    create table Student_Test(student_Id int, Question_Id int, Value_Code_Id int)

    insert question(Question_Id, Question)
    Values (10, 'Question 1')
    ,(20, 'Question 2')
    , (30, 'Question 1')
     ,(40, 'Question 1')

     Insert into Question_Value_Code(Question_Id , Value_Code_Id , Description )
     values 
      (10, 101, 'Yes')
     ,(10, 102, 'No')
     , (20, 201, 'Yes')
     , (20, 202, 'No')
     , (20, 203, 'Not Sure')
     , (30, 301, 'Yes')
     , (30, 302, 'No')
     , (40, 401, 'Yes')
     , (40, 402, 'No')

     insert into Student_Test(student_Id , Question_Id , Value_Code_Id )
     values
      (1, 10, 101)
     ,(1, 20, 201)
     ,(1, 30, 301)
     ,(1, 40, 401)

     , (2, 10, 102)
      ,(2, 20, 202)
     ,(2, 30, 302)
     ,(2, 40, 402)

     , (3, 10, 102)
     , (3, 20, 203)
     , (3, 30, 301)
     , (3, 40, 402)

     , (4, 10, 102)
     , (4, 20, 203)
     , (4, 30, 301)
     , (4, 40, 402)

      , (5, 10, 101)
     , (5, 20, 201)
     , (5, 30, 302)
     , (5, 40, 401)


     

     Input parameter @Value_Code_Id contains Value_Code_Id values.
     Output is student ids.

     input: 101, 102
     Output: 1, 2, 3, 4, 5
     
     input: 101, 102, 201  Reason: Out of all students only 1 answered question2 with value 201.
     Output: 1

     input: 101, 102, 203, 301
     Output: 3, 4

     input: 101, 102, 201, 202
     Output: 1, 2, 5

    Thanks,

    Wednesday, April 05, 2017 6:52 PM
  • Let me first phrase the problem as I understand it:

    List all students has answered all questions indicated by the input with any of the alternatives in the input.

    As I suspected, this is a problem that falls into the category of relational division. There is no reason to split the parameter more than once, that is, into a single table. The rest of the categorisation is done in T-SQL, fully relational.

    Before I go on, let me present some assumptions:

    In Student_Test the primary key is (Student_Id, Question_Id), while (Question_Id, Value_Code_Id) is a foriegn key to Question_Value_Code. Or rather would have been, it the value codes had been shared between the questions. Now it seems that they are unique. This makes the Question_Id column sort of redundant in Student_test. Then again, if value codes had not been unique, the input data would have to include the question ids.

    Anyway, here is the solution:

    DECLARE @Value_code_id TABLE (value_code int NOT NULL PRIMARY KEY)
    
    INSERT @Value_code_id (value_code)
       VALUES(101), (102), (201), (202)
    
    SELECT student_Id
    FROM   Student_Test S
    WHERE  EXISTS (SELECT *
                   FROM   @Value_code_id V
                   WHERE  S.Value_Code_Id = V.value_code)
    GROUP  BY student_Id
    HAVING COUNT(*) = (SELECT COUNT(DISTINCT Question_Id)
                       FROM   Question_Value_Code QVC
                       WHERE  EXISTS (SELECT *
                                      FROM   @Value_code_id V
                                      WHERE  QVC.Value_Code_Id = V.value_code))

    The key is the HAVING close. HAVING is akin to WHERE, but is computed after GROUP BY why it can include aggregate values. We should only include in the student that appears with all questions pointed to by the value codes. That is, where COUNT(*) is the same as the count in the subquery.

    I've tested the query with your input, and the result match in all cases but the second, where number 5 is also included. Which I think is correct.


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

    Thursday, April 06, 2017 8:02 PM
  • You are right, second case should return 5 also. Value code Ids are unique. I've another table

    Student_Info

    create table Student_Info (Student_Id int, City varchar(20), Grade varchar(20), Gender varchar(20))
    insert into Student_Info(Student_Id, City, Grade, Gender)
    values
    (1, 'Boston', 'Seventh', 'Male'),
    (2, 'Atlanta', 'Sixth', 'Male'),
    (3, 'Jersey', 'Ninth', 'Male'),
    (4, 'Jersey', 'Seventh', 'Female'),
    (5, 'Boston', 'Seventh', 'Male')

    each column of Student_Info also passed as parameters. If parameter present filter by that parameter. 

    To the above query, apply these parameters also:

    input: @City = 'Boston', @Value_Code_Id = 101, 102
    Output: 1,5 

    (3,4 and 5 shouldn't be here since their city is different)

    input: @City = 'Boston,Atlanta', @Value_Code_Id = 101,102
    Output: 1,2,5

    input: @city = "Jersey", @Grade ="Seventh",  @value_Code_id = 101,102,203, 301
    Output: 4

    input: @city = "Jersey", @Grade ="Ninth,Seventh",  @value_Code_id = 101,102,203, 301
    Output: 3,4

    input: @city = "Jersey", @Grade ="Ninth,Seventh", @Gender = "Female" @value_Code_id = 101,102,203, 301
    Output: 4

    input: @City = "Jersey, Boston";

    Output: 1,3,4,5

    input: @city = "Jersey", @Gender = "Female"

    Output: 4

    Taken input, outputs of original and applied Student_info column filters to above examples.

    Thanks for your help!



    • Edited by bluepink Friday, April 07, 2017 4:34 PM
    Friday, April 07, 2017 3:45 PM
  • That's not particularly tricky, but that a simple plain WHERE EXISTS filter:

      AND  EXISTS (SELECT *
                   FROM   Student_Info SI
                   JOIN   @Cities C ON SI.City = C.city
                   WHERE  S.student_Id = SI.Student_Id)

    If you also want to filter on gender etc, it may be better to join Student_Test to Student_Info, so you don't need it every subquery.

    I guess that if the string @city is empty or NULL, this means "all cities". You can write this as:

      AND  (nullif(@city, '') IS NULL OR
            EXISTS (SELECT *
                   FROM   Student_Info SI
                   JOIN   @Cities C ON SI.City = C.city
                   WHERE  S.student_Id = SI.Student_Id))


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

    Friday, April 07, 2017 7:04 PM
  • But suppose, if all/some parameters have values, I need to apply all parameters at once. All params are @value_code_id, @City, @gender, @grade. If only @value_code_Id and one of the parameter correspond to column of Student_info table then inner join between Studen_Test, Student_info fine. But filtering other columns of Student_info when remaining columns params have values?
    Saturday, April 08, 2017 12:59 AM
  • But suppose, if all/some parameters have values, I need to apply all parameters at once. All params are @value_code_id, @City, @gender, @grade. If only @value_code_Id and one of the parameter correspond to column of Student_info table then inner join between Studen_Test, Student_info fine. But filtering other columns of Student_info when remaining columns params have values?

    So that was what I tried to discuss in my previous post. Read it closer and play around.

    Saturday, April 08, 2017 9:03 AM