CODEWITHSUNDEEP
sqloracle-database
Adi
Adi

Reputation: 309

Dynamic column name based on day from date

I need to accomplish a table with below template. I need to have for example data like Last Name, First Name from two tables and then the data is stored in separate records for each individual. How can I do this ?

Table resulting from Query

+-----------------------------------------+
| Last name | First name | 1  | 2 |..| 31 |
+-----------------------------------------+
|  Andrew   |   test     |  8 | 0 |..| 8  |
+-----------------------------------------+
|  John     |   him      |  0 | 0 |..| 0  |
+-----------------------------------------+

Table users

+-------------------------------+
| id |  first_name  | last_name |
+-------------------------------+
| 1  |     test     |   Andrew  |
+-------------------------------+
| 2  |      him     |    John   |
+-------------------------------+

Table work

+-------------------------------------------------------+
| id  |  person_id  |  date_created |  number_of_hours  |
+-------------------------------------------------------+
| 1   |      1      |   01.01.2019  |        8          |
+-------------------------------------------------------+
| 2   |      1      |   02.01.2019  |        0          |
+-------------------------------------------------------+
...
+-------------------------------------------------------+
| 31  |      1      |   31.01.2019  |        8          |
+-------------------------------------------------------+
| 32  |      2      |   01.01.2019  |        0          |
+-------------------------------------------------------+
...

I have tried to get also the day name Like Monday,..,Sunday and i have this

select n, case TO_CHAR( to_date(n||'.01','dd.mm'),'D')
          when '1' then n||' D'
          when '2' then 'L'
          when '3' then 'Ma'
          when '4' then 'Mi'
          when '5' then 'J'
          when '6' then 'V'
          when '7' then 'S' else 'no data'
       end d from
   (select rownum n from dual connect by level <= 31)
where n >= 1

And i know i can't use like this, but how i can do this query to work ? Thank you

SELECT
    a.last_name
   ,a.first_name
   ,  -- {this is what i can't figure out how to do it}
FROM users a
INNER JOIN work b
on a.id = b.person_id

Upvotes: 1

Views: 525

Answers (2)

and
and

Reputation: 127

As I understood your question, you would like to group the hours from table work by day and user. This can be achived by using the pivot clause:

    select * FROM (
    SELECT
        a.last_name
       ,a.first_name
       ,w.date_created
       ,w.number_of_hours
    FROM users a
        JOIN work w on a.id = w.person_id
    ) PIVOT (
      sum(number_of_hours)
      FOR date_created
      IN (date '2019-01-01' as "2019-01-01", DATE '2019-01-01' as "2019-01-02")
      )

If you just want it to group by day, then this can be achived as follows:

    select * FROM (
    SELECT
        a.last_name
       ,a.first_name
       ,to_char(w.date_created, 'D') as day_of_week
       ,w.number_of_hours
    FROM users a
        JOIN work w on a.id = w.person_id
    ) PIVOT (
      sum(number_of_hours)
      FOR day_of_week
      IN (1,2,3,4,5,6,7)
      )

Upvotes: 4

Tomasz
Tomasz

Reputation: 610

If you would like to store lastname, firstname, and than data from days between 1 and 31, lets try a "pivot" clause (works in Oracle). Should help

select * from (-- subquery
  select a.last_name ,a.first_name
  ,b.day_number -- between 1 and 31
  ,b.number_of_hours
  from users a
  inner join work b on a.id = b.person_id
) pivot (-- pibot clouse
  sum(number_of_hours)
  for day_number in (1,2, ..., 31)
)

Upvotes: 3

Related Questions