3

i try to build a PIVOT Table in Oracle APEX 5. I have 3 tables:

Table CAB
ID Number
CAB_DATE DATE

Table CAB_DEP_TO_CAB
CAB_ID VARCHAR2
DEP_ID VARCHAR2

Table CAB_DEP
ID Number
DEP_NAME VARCHAR2

i tried:

SELECT DISTINCT 
    LISTAGG('''' || CAB_DATE || ''' AS ' || CAB_DATE,',')
        WITHIN GROUP (ORDER BY CAB_DATE) AS temp_in_statement 
    FROM (SELECT DISTINCT CAB_DATE FROM CAB);

this works without problems but not the PIVOT:

SELECT * FROM 
    (SELECT cd.dep_name, c.cab_date, d.dep_id FROM cab c INNER JOIN cab_dep_to_cab d ON c.id = d.cab_id INNER JOIN cab_dep cd ON d.dep_id like ('%' || cd.id || '%') )
    PIVOT (SUM(dep_id) FOR c.cab_date IN (&str_in_statement));

It gives back the Error: ORA-01748 "only simple column names allowed here"

Can someone see my misstake? :O

btw. i want a result like this: enter image description here

btw. i tried to use the code from here: Dynamic pivot in oracle sql

MT0
  • 143,790
  • 11
  • 59
  • 117
TheFlori
  • 69
  • 1
  • 9

1 Answers1

2

I think the issue here is that you use "c.cab_date" instead of "cab_date" in the pivot function. This should work (not sure why you have such a strange join condition on the second join, I replaced it by a more reasonable one):

SELECT * FROM 
    (SELECT cd.dep_name
          , c.cab_date
          , d.dep_id
       FROM cab c 
      INNER JOIN cab_dep_to_cab d ON c.id = d.cab_id 
      INNER JOIN cab_dep cd ON d.dep_id = cd.id
    )
 PIVOT (SUM(dep_id) FOR cab_date IN (&str_in_statement));

And if I may add some small hints for your tables design: I would recommend to name the column like "department ID" always the same, that will make the code more readable in my opinion, but this is just personal preference of course ;)

I'd name the things this way:

Table CAB
CAB_ID number
CAB_DATE date

Table DEPARTMENTS
DEPARTMENT_ID number
DEPARTMENT_NAME varchar2

Table DEPARTMENT_CABS
CAB_ID number  -- not varchar as in your example!
DEPARTMENT_ID number  -- not varchar as in your example!
david
  • 36
  • 3