2

I am trying to write a query that given a table like the following one

PROTOCOL SURVEY DATE ENTITY CREATION_TIME
PROTOCOL01 SURVEY01 DATE_01 ENTITY_01 DATE_OF_CREATION_A
PROTOCOL01 SURVEY01 DATE_01 ENTITY_01 DATE_OF_CREATION_B
PROTOCOL02 SURVEY02 DATE_02 ENTITY_02 DATE_OF_CREATION_C
PROTOCOL02 SURVEY02 DATE_02 ENTITY_02 DATE_OF_CREATION_D
PROTOCOL02 SURVEY02 DATE_02 ENTITY_02 DATE_OF_CREATION_E
PROTOCOL02 SURVEY02 DATE_02 ENTITY_02 DATE_OF_CREATION_F
PROTOCOL03 SURVEY03 DATE_03 ENTITY_03 DATE_OF_CREATION_G
PROTOCOL03 SURVEY03 DATE_03 ENTITY_03 DATE_OF_CREATION_H
PROTOCOL03 SURVEY03 DATE_03 ENTITY_03 DATE_OF_CREATION_I

will give me this result:

PROTOCOL SURVEY DATE ENTITY COUNTER CREATION_TIME 1 CREATION_TIME 2 CREATION_TIME 3 CREATION_TIME 4
PROTOCOL01 SURVEY01 DATE_01 ENTITY_01 2 DATE_OF_CREATION_A DATE_OF_CREATION_B
PROTOCOL02 SURVEY02 DATE_02 ENTITY_02 4 DATE_OF_CREATION_C DATE_OF_CREATION_D DATE_OF_CREATION_E DATE_OF_CREATION_F
PROTOCOL03 SURVEY03 DATE_03 ENTITY_03 3 DATE_OF_CREATION_G DATE_OF_CREATION_H DATE_OF_CREATION_I

That is, a query that will find all records in my table that have the same set of PROTOCOL, SURVEY, DATE and ENTITY values, count them in a COUNTER - only if there's at least 2 of them - and then also display (all in one row) all the different values of the column CREATION_TIME given a set of identical PROTOCOL, SURVEY, DATE and ENTITY values.

This is what I was able to write so far:

select protocol, survey, date, entity, count(*) counter from MYTABLE
where protocol is not null
group by protocol, survey, date, entity having count(*) > 1
order by count(*) desc

But I do not know how to add to this the list of all the different CREATION_TIMEs.

I have tried to use UNPIVOT but without much success.

Any help would be much appreciated, thanks very much in advance!

lucia de finetti
  • 219
  • 2
  • 8
  • 1
    Your title says Oracle SQL, but your tag says mysql. Which database are you really using? – Bill Karwin Oct 07 '22 at 15:45
  • @BillKarwin thanks for taking the time to reply: I'm using Oracle SQL but I cannot find the relevant tag. I've removed MySQL from the ones I had included though. – lucia de finetti Oct 07 '22 at 17:12

0 Answers0