techlearn
asked on
Oracle SQL
I have a following table with the datasets as a example.
Date Type
01/02/2014 12:32 GEO
01/03/2014 14:43 GEO
01/04/2014 19:34 GEO
01/07/2014 01:23 SCI
01/07/2014 02:23 ENG
02/05/2015 12:33 GEO
02/02/2015 01:33 MAT
I want to group the date by month and the result should be like this:
Month GEO SCI MAT ENG Total
01/01/2014 3 1 0 1 5
02/01/2015 1 0 1 0 2
Date Type
01/02/2014 12:32 GEO
01/03/2014 14:43 GEO
01/04/2014 19:34 GEO
01/07/2014 01:23 SCI
01/07/2014 02:23 ENG
02/05/2015 12:33 GEO
02/02/2015 01:33 MAT
I want to group the date by month and the result should be like this:
Month GEO SCI MAT ENG Total
01/01/2014 3 1 0 1 5
02/01/2015 1 0 1 0 2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Mark Geerlings
The output you are asking for is called a "matrix" or "cross-tab" report. Simple SQL does not do this. So, you need the more-complex examples like what others have suggested, or you need to use a reporting tool that can do this for you.
if the expected columns are known before hand it can be done, as illustrated above in 2 different forms.
you could also do a bunch of self-joins or unions but that's horribly inefficient
you could also do a bunch of self-joins or unions but that's horribly inefficient
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.