sql pivot with no aggregate

Question

Whilst trying to pivot a sql table I came across this post Here . By using this method I have created a query. However i have now realised that it of course aggregates the results with the MAX function. However I need the Colum to pivot but for all occurrences to be show. Code taken from above post.

  SELECT dy,
         MAX(CASE WHEN period = 1 THEN subj ELSE NULL END) AS P1,
         MAX(CASE WHEN period = 2 THEN subj ELSE NULL END) AS P2,    
    FROM Classes
GROUP BY dy

So in essence I wish to use this but without the max function ? Any Ideas?

Edit Example data

Day   Period    Subject

Mon   1         Ch
Mon   2         Ph
Tue   1         Ph
Tue   2         Ele
Mon   1         Ch
Mon   2         Ph
Tue   1         Ph
Tue   2         Ele

example output

Day   P1   P2   

Mon   Ch   Ph   
Mon   Ch   Ph   
Tue   Ph   Ele  
Tue   Ph   Ele  

so basicly if the data is entered twice it appears twice...

Edit actual sql..

  SELECT other
         MAX(CASE WHEN period = 1 THEN table2.subj ELSE NULL END) AS P1,
         MAX(CASE WHEN period = 2 THEN table2.subj ELSE NULL END) AS P2    
    FROM table1
left join table2 on table2.ID = subject

GROUP BY other

Example data

Table1

Dy   Period    Subject other

Mon   1         1       1
Mon   2         2       1
Tue   1         3       2
Tue   2         4       2
Mon   1         5       3
Mon   2         6       3
Tue   1         7       4
Tue   2         8       4

table2

ID  Subj
1 ch
2 ph
3 ph
4 ele
5 ch
6 ph
7 ph
8 Ele

example output

Day   P1   P2   other

Mon   Ch   Ph   1
Mon   Ch   Ph   3
Tue   Ph   Ele  2
Tue   Ph   Ele  4

Answer 1

Remove the Max Functions and the group by

SELECT dy, CASE WHEN period = 1 THEN subj ELSE NULL END AS P1, CASE WHEN period = 2 THEN subj ELSE NULL END AS P2
FROM Classes

Answer 2

Believe it or not, you don't have to remove the MAX() aggregate function to get all results. You can get the rows you want simply by adding more criteria to your GROUP BY clause such that you will get all the results you want.

For example, if dy is unique, you will get all results, but if you have another column that makes your GROUP BY clause unique, you can just add it, and you'll get all results. Let's say that column is called `id':

  SELECT dy,id,
         MAX(CASE WHEN period = 1 THEN subj ELSE NULL END) AS P1,
         MAX(CASE WHEN period = 2 THEN subj ELSE NULL END) AS P2,    
    FROM Classes
GROUP BY dy, id

Answer 3

Your example data and your desired output don't match up. Where are the Mth, CS2, Lab, and Hu values coming from in the output? They're not in the input.

It's not entirely clear what you're trying to do, but assuming that the separate rows in your output for (for instance) Mon representing two different students, you need to include the distinguishing column (StudentID) in your SELECT list and the GROUP BY clause. Then you can safely use MAX not to aggregate (since there will only be one value per output cell) but rather to trick the engine into doing the pivot for you.

Here's the SQL with the student ID included:

SELECT student_id, dy,
     MAX(CASE WHEN period = 1 THEN subj ELSE NULL END) AS P1,
     MAX(CASE WHEN period = 2 THEN subj ELSE NULL END) AS P2,    
FROM Classes
GROUP BY student_id, dy

This will return one record for each student for each day showing their daily class schedule. If you want the output sorted by day then student, reverse the order of student_id and dy in the SELECT and GROUP BY clauses.