CODEWITHSUNDEEP
sqloracle11goracle10g
Jacob
Jacob

Reputation: 14731

Get Day as Column Header from Date Records

I have the following data in table EMP_DATES.

enter image description here

I would like to achieve the following output from the table:

enter image description here

How can I do this. I have used the following SQL query, however not sure how to get Day as column header.

SELECT TO_CHAR (start_date, 'Day'),
       TO_CHAR (completion_date, 'Day'),
       start_date,
       completion_date,
       ROUND ( (completion_date - start_date) * 24, 1)
  FROM emp_dates

Table:

CREATE TABLE EMP_DATES
(
   START_DATE        DATE,
   COMPLETION_DATE   DATE
);


SET DEFINE OFF;

INSERT INTO EMP_DATES (START_DATE, COMPLETION_DATE)
     VALUES (
               TO_DATE ('02/29/2016 12:24:25', 'MM/DD/YYYY HH24:MI:SS'),
               TO_DATE ('02/29/2016 15:30:00', 'MM/DD/YYYY HH24:MI:SS'));

INSERT INTO EMP_DATES (START_DATE, COMPLETION_DATE)
     VALUES (
               TO_DATE ('03/01/2016 07:00:00', 'MM/DD/YYYY HH24:MI:SS'),
               TO_DATE ('03/01/2016 11:54:25', 'MM/DD/YYYY HH24:MI:SS'));

COMMIT;

Upvotes: 0

Views: 301

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

You're trying to pivot your rows into columns, but as you're on 10g the actual PIVOT clause isn't available; so you need to do it the expanded way with case statements and aggregation:

SELECT
  SUM(CASE WHEN TO_CHAR (start_date, 'FMDay') = 'Sunday' THEN
    ROUND ( (completion_date - start_date) * 24, 1) END) AS "Sunday",
  SUM(CASE WHEN TO_CHAR (start_date, 'FMDay') = 'Monday' THEN
    ROUND ( (completion_date - start_date) * 24, 1) END) AS "Monday",
  SUM(CASE WHEN TO_CHAR (start_date, 'FMDay') = 'Tuesday' THEN
    ROUND ( (completion_date - start_date) * 24, 1) END) AS "Tuesday",
  SUM(CASE WHEN TO_CHAR (start_date, 'FMDay') = 'Wednesday' THEN
    ROUND ( (completion_date - start_date) * 24, 1) END) AS "Wednesday",
  SUM(CASE WHEN TO_CHAR (start_date, 'FMDay') = 'Thursday' THEN
    ROUND ( (completion_date - start_date) * 24, 1) END) AS "Thursday",
  SUM(CASE WHEN TO_CHAR (start_date, 'FMDay') = 'Friday' THEN
    ROUND ( (completion_date - start_date) * 24, 1) END) AS "Friday",
  SUM(CASE WHEN TO_CHAR (start_date, 'FMDay') = 'Saturday' THEN
    ROUND ( (completion_date - start_date) * 24, 1) END) AS "Saturday"
FROM emp_dates;

    Sunday     Monday    Tuesday  Wednesday   Thursday     Friday   Saturday
---------- ---------- ---------- ---------- ---------- ---------- ----------
                  3.1        4.9

The 'FM' format modifier stops the value produced by to_char() being padded by spaces, which is what happens by default with 'Day' and 'Dy'; without that the text literals would also need to be padded, e.g. 'Monday '.

You could use a subquery to simplify the code a little:

SELECT
  SUM(CASE WHEN dy = 'Sun' THEN hours END) AS "Sunday",
  SUM(CASE WHEN dy = 'Mon' THEN hours END) AS "Monday",
  SUM(CASE WHEN dy = 'Tue' THEN hours END) AS "Tuesday",
  SUM(CASE WHEN dy = 'Wed' THEN hours END) AS "Wednesday",
  SUM(CASE WHEN dy = 'Thu' THEN hours END) AS "Thursday",
  SUM(CASE WHEN dy = 'Fri' THEN hours END) AS "Friday",
  SUM(CASE WHEN dy = 'Sat' THEN hours END) AS "Saturday"
FROM (
  SELECT TO_CHAR (start_date, 'FMDy', 'NLS_DATE_LANGUAGE=English') AS dy,
    ROUND ( (completion_date - start_date) * 24, 1) AS hours
  FROM emp_dates
);

    Sunday     Monday    Tuesday  Wednesday   Thursday     Friday   Saturday
---------- ---------- ---------- ---------- ---------- ---------- ----------
                  3.1        4.9

I've also changed that use the abbreviated day name just because it's shorter, and specified the language to use for that - in case the client session running this isn't in English, which would make the comparison with the string literals fail. (You can read more about that in the documentation).

In the first version, for example, if the session language was French, the to_char() would get 'Lundi', which would not match the fixed value 'Monday'. You could also use the day number for the comparison but that is also affected by NLS settings, and can't be changed the way the language can.

Upvotes: 2

Related Questions