CODEWITHSUNDEEP
sql-servergroup-byrows
Davit Markarian
Davit Markarian

Reputation: 3

SQL Server showing values grouped in columns from rows based on specific field value

I have this table of information

fk_studentID | fk_courseID | fk_educationalSemesterID | value
-------------+-------------+--------------------------+------    
    1        |      1      |          1               |  18 
    1        |      2      |          1               |  18
    1        |      3      |          1               |  14
    1        |      4      |          1               |  17
    1        |      5      |          1               |  14
    1        |      6      |          1               |  17
    1        |      8      |          1               |  18
    1        |      1      |          2               |  19
    1        |      2      |          2               |  19
    1        |      3      |          2               |  18
    1        |      4      |          2               |  15
    1        |      4      |          2               |  19
    1        |      5      |          2               |  20
    1        |      1      |          3               |  17
    1        |      8      |          3               |  20

Need to prepare output result as:

fk_studentID | fk_courseID | 1st Semester | 2nd Semester | 3rd Semester
-------------+-------------+--------------+--------------+-------------
    1        |       1     |     18       |     19       |     17
    1        |       2     |     18       |     19       | 
    1        |       3     |     14       |     18       | 
    1        |       4     |     17       |     15       | 
    1        |       5     |     14       |     19       | 
    1        |       6     |     17       |              | 
    1        |       8     |     18       |     20       |     20

Please help

Upvotes: 0

Views: 24

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520958

I think you are just looking for a standard pivot query:

SELECT fk_studentID,
       fk_courseID,
       MAX(CASE WHEN fk_educationalSemesterID = 1 THEN value END) AS 1st_semester,
       MAX(CASE WHEN fk_educationalSemesterID = 2 THEN value END) AS 2nd_semester,
       MAX(CASE WHEN fk_educationalSemesterID = 3 THEN value END) AS 3rd_semester
FROM yourTable
GROUP BY fk_studentID,
         fk_courseID

Upvotes: 2

Related Questions