0

I have gone through a lot of similar questions(row to column conversion in mysql) and solutions provided for the same but those solutions were not working for me!

Expected Result:

Name |S1   |S2   |S3   |S4   |S5   |S6
----------------------------------------
ABC  |Pass |Fail |Fail |Pass |Pass |Fail


SQL Query:
----------

SELECT Name,
 (CASE WHEN (Semester = 'S1'  AND Result= 'Pass') THEN 'Pass' ELSE 'Fail' END) AS S1,
 (CASE WHEN (Semester = 'S2'  AND Result= 'Pass') THEN 'Pass' ELSE 'Fail' END) AS S2,
 (CASE WHEN (Semester = 'S3'  AND Result= 'Pass') THEN 'Pass' ELSE 'Fail' END) AS S3,
 (CASE WHEN (Semester = 'S4'  AND Result= 'Pass') THEN 'Pass' ELSE 'Fail' END) AS S4,
 (CASE WHEN (Semester = 'S5'  AND Result= 'Pass') THEN 'Pass' ELSE 'Fail' END) AS S5,
 (CASE WHEN (Semester = 'S6'  AND Result= 'Pass') THEN 'Pass' ELSE 'Fail' END) AS S6
FROM pivot_example
-- GROUP BY Semester

SQL Output:

Name |S1   |S2   |S3   |S4   |S5   |S6
--------------------------------------------------
ABC  |Pass |Fail |Fail |Fail |Fail |Fail
ABC  |Fail |Fail |Fail |Fail |Fail |Fail
ABC  |Fail |Fail |Fail |Fail |Fail |Fail
ABC  |Fail |Fail |Fail |Pass |Fail |Fail
ABC  |Fail |Fail |Fail |Fail |Pass |Fail
ABC  |Fail |Fail |Fail |Fail |Fail |Fail
Barmar
  • 741,623
  • 53
  • 500
  • 612
Santosh
  • 875
  • 5
  • 14
  • 33

1 Answers1

1

a) in order to produce one row, change:

GROUP BY Semester

into:

GROUP BY Name

b) for the pass/fail values it might be required to query, depending how the table looks alike.

Martin Zeitler
  • 1
  • 19
  • 155
  • 216
  • Error: SELECT list is not in GROUP BY clause and contains nonaggregated column 'pivot_example.Semester' which is not functionally dependent on columns in GROUP BY clause – Santosh Jul 09 '19 at 00:22
  • @Santosh That would only happen if you have `SELECT Semester` and `GROUP BY Name` – Barmar Jul 09 '19 at 00:27
  • @Barmar also thought so, because the `GROUP BY` not necessarily requires aggregated columns - but a selected column. for the value columns it's hard to tell without having seen the table. – Martin Zeitler Jul 09 '19 at 00:31
  • 1
    When doing a pivot, you always group by the column that all the other columns are pivoted attributes of. – Barmar Jul 09 '19 at 00:38