views:

55

answers:

3

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
A: 

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

automatic
Without the GROUP BY and aggregate, the result set isn't flatten -- you'll have numerous NULL values to deal with.
OMG Ponies
My answer is no longer correct, based on the edited question.
automatic
A: 

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
Dave Markle
That's only constricting the grouping -- not dealing with the request to not use MAX.
OMG Ponies
Thanks for this I think that not using max may just be me thinking that was the problem. After applying a extra column i still get the same result only one row of data when I want all of them.. Any Ideas ?
Tom
OMG Ponies is right... you need to give us some more information about the data you are querying. Here's the thing -- just adding any other column won't do. It has to be a column or a set of columns that make the GROUP BY clause unique. So here's a trick... What's the primary key on the table? Change your GROUP BY clause so that it includes your table's primary key at the end and you'll get all of the rows. I promise.
Dave Markle
A: 

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.

Larry Lustig
sorry your apsolutly right that output was not relevent at all. However you did assume right. and thank you for pointing out the need for the (StudentID) to be in the select statement as well. However after doing that for some reason it still on comes back with one result. Any chances of sample code ? As i must be doing some thing wrong for it not to work.
Tom
I added the SQL to the answer, above.
Larry Lustig
Would the fact that rather then the achtual values they are joined rather then just the value be causing the problem ? As your code works fine untill i add the joins in ? many thanks!
Tom
Post your actual SQL. It sounds like it differs substantially from what you wrote in the question.
Larry Lustig
have added edited original post...
Tom