Forum Stats

  • 3,838,263 Users
  • 2,262,349 Discussions
  • 7,900,568 Comments

Discussions

IDENTIFY UNIQUE ROW IN TABLE WHILE USING PIVOT AND GROUP BY

User_N31VH
User_N31VH Member Posts: 1 Employee

Hi,

I need help with identifying unique row after fetching data using pivot and group by.


Data in table Table_1:

ID is the primary key in table

Delivery_id + Role_id also identifies a unique row in the table


ID | Project | Deliverable | Role |FTE_Days | Delivery_ID | Role_ID

1. | p1        | D1               | R1    | 1             | 11                | 10

2. | p1        | D1               | R2    | 5             | 11                | 11

3. | p1        | D2               | R1    | 1             | 13               | 10


In UI, data is displayed as below from table_1 (using pivot and group by):

Pivot over 'Role' and Group by 'Deliverable'

Project | Deliverable | Role R1 | Role R2

 p1        | D1               | 1            | 5

 p1        | D2               | 5            |


Now when user edits Role R1:

Project | Deliverable | Role R1 | Role R2

 p1        | D1               | 2           | 5

 p1        | D2               | 6           |


DB data:

ID | Project | Deliverable | Role |FTE_Days | Delivery ID | Role ID

1. | p1         | D1               | R1    |  2             | 11                | 10

2. | p1         | D1               | R2    |  6             | 11                | 11

3. | p1         | D2               | R1    | 1             | 13                | 10

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,229 Red Diamond

    Hi, @User_N31VH

    Sorry, it's not clear what you want.

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

    In UI, data is displayed as below from table_1 (using pivot and group by):

    Post the query you are using to do that.

  • Stax
    Stax Member Posts: 120 Bronze Badge

    add id into "pivot" table

    ps

    1)  p1        | D2              | 5           |

    5 not 1?

    2)

     p1        | D2              | 6           |

    2. | p1        | D1              | R2   |  6             | 11               | 11

    p1|D2<>p1|D1 ?