1

I have a SQL Table like this:

TABLE 1(ANSWERS)

|     ID         | CODE_STUDENT| ANSWERS
+----------------+-------------+-------------------
| 1              | 1           | 1,2,4
| 2              | 2           | 3,2,3

TABLE 2(STUDENT)

|     ID         | NAME        | GENDER
+----------------+-------------+-------------------
| 1              | ANDY        | MAN
| 2              | MARRY       | WOMAN

how do I get the select query results like this

| STUDENT.ID     | STUDENT.NAME| QUESTION          | ANSWERS.ANSWERS
+----------------+-------------+-------------------+-------------------
| 1              | ANDY        | 1                 | 1
| 1              | ANDY        | 2                 | 2
| 1              | ANDY        | 3                 | 4
| 2              | MARRY       | 1                 | 3
| 2              | MARRY       | 2                 | 2
| 2              | MARRY       | 3                 | 3

Basically split my data at the comma into individual rows and join two table?

the question values, can be obtained by counting the number of indexes of the answers

Tedy Fernando
  • 11
  • 3
  • There's no standard SQL function for this because *you aren't supposed to store multiple values in a table cell*. That violates the most basic design rule. Different databases have different functions for this – Panagiotis Kanavos Oct 17 '19 at 06:53
  • 2
    MySQL != SQL Server ... which one are you actually using? – Tim Biegeleisen Oct 17 '19 at 06:53
  • Can you share your QUESTION table as well? – ramzan ali Oct 17 '19 at 06:55
  • The real solution is to use *multiple rows* per answer. The `Answers` table isn't really an Answers table, it's a M-N StudentAnswers table. The `AnswerID` deserves to be a column of its own, with a FK to the actual `Answers` table. This will allow you to guard against *duplicate answers* – Panagiotis Kanavos Oct 17 '19 at 06:55
  • With properly designed tables, getting all student questions and answers would be as simple as `select S.ID,s.Name,a.QuestionID, a.ID as AnswerID from student s inner join StudentAnswers sa on s.ID=sa.CODE_Student inner join Answers a on sa.ID=a.ID` – Panagiotis Kanavos Oct 17 '19 at 07:06
  • You can Use Below Query - SELECT CODE_STUDENT AS STUDENT_ID,NAME AS STUDENT_NAME,QUESTION,Answer FROM ( SELECT ANS_ID,CODE_STUDENT, PARSENAME(REPLACE(Split.a.value('.', 'VARCHAR(100)'),'-','.'),1) 'Answer' , ROW_NUMBER() OVER(PARTITION BY CODE_STUDENT ORDER BY CODE_STUDENT) AS QUESTION FROM ( SELECT ANS_ID,CODE_STUDENT, CAST ('' + REPLACE(ANS_NO, ',', '') + '' AS XML) AS Data FROM ANSWERS ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a) )temp_ANS INNER JOIN STUDENT ON temp_ANS.CODE_STUDENT = STUDENT.ST_ID – Dev Oct 17 '19 at 09:03

0 Answers0