If I figured out correctly what you're doing, you could also do it using unpivot, something like this with the separate columns in 2 CTEs and join them side by side using number for row_number:
;with D1 as (
select row_number() over (order by (select null)) as RN, p8_14
from (
select prodt_8, prodt_9, prodt_10
from test
) p unpivot (
p8_14 for colname1 in (prodt_8, prodt_9, prodt_10)
) as up),
D2 as (
select row_number() over (order by (select null)) as RN, p15_17
from (
select prodt_15, prodt_16, prodt_17
from test
) p unpivot (
p15_17 for colname1 in (prodt_15, prodt_16, prodt_17)
) as up)
select p8_14, p15_17 from D1 join D2 on D1.RN = D2.RN
That's just couple of the columns, made also SQL Fiddle: http://sqlfiddle.com/#!6/33d41/1
The columns ABSENT, LATE and PRESENT in your output represent totals per student, whereas the date columns represent details per student, and details are the pivoted columns. In order to get such output with PIVOT, your source must provide totals on the same row with details.
The source in your query is not providing that kind of information. It is grouping by student_id, attendance_Date, attendance_id, and that grouping does not have any effect since there is always one row per student and date.
In order to have both details and totals on the same row, you can use window aggregation, like this:
SELECT
student_id,
attendance_Date,
attendance_id,
SUM (DECODE (Attendance_id, 'ABSENT', 1, 0)) OVER (PARTITION BY student_id) AS ABSENT,
SUM (DECODE (Attendance_ID, 'LATE', 1, 0)) OVER (PARTITION BY student_id) AS LATE,
SUM (DECODE (Attendance_ID, 'PRESENT', 1, 0)) OVER (PARTITION BY student_id) AS PRESENT
FROM
sample
Note absence of GROUP BY. The OVER clause makes SUM a window aggregate function. With a window function you can return aggregate data along with detail (non-aggregate) data. The query above returns output like this:
STUDENT_ID ATTENDANCE_DATE ATTENDANCE_ID ABSENT LATE PRESENT
---------- --------------- ------------- ------ ---- -------
SCM-026020 4/4/2016 LATE 1 1 0
SCM-026020 5/4/2016 ABSENT 1 1 0
SCM-026021 5/4/2016 ABSENT 2 0 0
SCM-026021 4/4/2016 ABSENT 2 0 0
SCM-026022 4/4/2016 PRESENT 0 0 2
SCM-026022 5/4/2016 PRESENT 0 0 2
That is, it returns details per date and student along with totals per student. Using that source, the PIVOT clause provides the expected result:
STUDENT_ID ABSENT LATE PRESENT 4/4/2016 5/4/2016
---------- ------ ---- ------- -------- --------
SCM-026020 1 1 0 LATE ABSENT
SCM-026021 2 0 0 ABSENT ABSENT
SCM-026022 0 0 2 PRESENT PRESENT
Best Answer
There are a few ways that you can perform this data transformation. You have access to the
PIVOTfunction then that will be the easiest, but if not then you can use an aggregate function and aCASE.Aggregate /Case version:
See SQL Fiddle with Demo
Static Pivot:
See SQL Fiddle with Demo
Dynamic Version:
The two versions above work great if you have a known number of values, but if your values are unknown, then you will want to implement dynamic sql and in Oracle you can use a procedure:
Then you return the results, you will use:
The results are the same with all versions: