Home » SQL & PL/SQL » SQL & PL/SQL » Transform row data into columns (Oracle 19c, Linux)
Transform row data into columns [message #689324] Wed, 22 November 2023 11:21 Go to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
Requirement :

The rows needs to be converted into columns for each caseid in order of sort id and notes.

CREATE TABLE A
(
  CID     NUMBER                            NOT NULL,
  CNUM    VARCHAR2(20 CHAR),
  SORT_ID     NUMBER,
  NOTES       VARCHAR2(4000 BYTE),
  ENTRY_DATE  DATE
);


Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (264990,'2023-010660',2,'ER Sent',to_date('28-JUL-23','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (264990,'2023-010660',4,'ER Sent',to_date('18-AUG-23','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (264990,'2023-010660',5,'ER Received',to_date('31-AUG-23','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (264990,'2023-010660',6,'ER Sent',to_date('11-SEP-23','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (264990,'2023-010660',7,'ER Sent',to_date('05-OCT-23','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (264991,'2023-011660',3,'ER Sent',to_date('18-JUL-23','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (264991,'2023-011660',5,'ER Received',to_date('19-JUL-23','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (264991,'2023-011660',6,'ER Sent',to_date('18-AUG-23','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (264991,'2023-011660',7,'ER Received',to_date('31-AUG-23','DD-MON-RR'));
Ex :

Consider rows 1,2,3 for cid : 264990

There were 2 ER Sents followed by ER Received. Since ER Received is after ER Sent 2 So we have value in ER Received 2 and blank in ER Received 1


Output :

/forum/fa/14773/0/
[attached]
  • Attachment: image.png
    (Size: 6.22KB, Downloaded 2439 times)

Report message to a moderator

Re: Transform row data into columns [message #689325 is a reply to message #689324] Wed, 22 November 2023 11:43 Go to previous messageGo to next message
mathguy
Messages: 108
Registered: January 2023
Senior Member
Examples are good, but they don't substitute for a complete problem specification. For example: what if there are two Sent rows followed by two Received rows? What should be the output then? If such cases are not possible, you should say so; and then, say exactly what cases are possible and how they should be handled.

Also - can there be more than 4 Sent rows for the same CID and CNUM? And if so, what should appear in the output?

Report message to a moderator

Re: Transform row data into columns [message #689326 is a reply to message #689325] Wed, 22 November 2023 12:46 Go to previous messageGo to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
Thank you for the response.

I just found out from the team :


Examples are good, but they don't substitute for a complete problem specification. For example: what if there are two Sent rows followed by two Received rows? What should be the output then? If such cases are not possible, you should say so; and then, say exactly what cases are possible and how they should be handled.

-- It looks like this is technically not possible to get two responses back. If so, 2 responses should be to 2 Sents. That was very good scenario. Havent thought of it looking at some of the cases.

Also - can there be more than 4 Sent rows for the same CID and CNUM? And if so, what should appear in the output?

-- Yes those columns can be Sent 5 and Sent 6 ....We dont know how many. But is it possible to hardcode like 10 columns (max)  till Sent 10 and code accordingly.

Report message to a moderator

Re: Transform row data into columns [message #689327 is a reply to message #689326] Wed, 22 November 2023 13:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Assuming the rows (converted to columns) are displayed in the order of their ENTRY_DATE as the example seems to show then:
SQL> with
  2    data as (
  3      select CID, CNUM, NOTES, ENTRY_DATE,
  4             count(decode(NOTES,'ER Sent',NOTES)) over (partition by CID, CNUM order by ENTRY_DATE)
  5               cntsent,
  6             count(decode(NOTES,'ER Received',NOTES)) over (partition by CID, CNUM order by ENTRY_DATE)
  7               cntrcv
  8         from a )
  9  select *
 10  from ( select CID, CNUM, ENTRY_DATE,
 11                2 * greatest(cntsent,cntrcv) - decode(NOTES, 'ER Sent', 1, 0) colnb
 12         from data )
 13  pivot (max(ENTRY_DATE)
 14         for colnb in (1 "ER Sent1", 2 "ER Received1", 3 "ER Sent2", 4 "ER Received2",
 15                       5 "ER Sent3", 6 "ER Received3", 7 "ER Sent4", 8 "ER Received4"))
 16  order by CID, CNUM
 17  /
       CID CNUM                 ER Sent1    ER Received ER Sent2    ER Received ER Sent3    ER Received ER Sent4    ER Received
---------- -------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
    264990 2023-010660          28-JUL-2023             18-AUG-2023 31-AUG-2023 11-SEP-2023             05-OCT-2023
    264991 2023-011660          18-JUL-2023 19-JUL-2023 18-AUG-2023 31-AUG-2023

2 rows selected.

Quote:
Yes those columns can be Sent 5 and Sent 6 ....We dont know how many. But is it possible to hardcode like 10 columns (max)  till Sent 10 and code accordingly.

SQL does not handle dynamic number of columns you have to determine the max number of them and write the query accordingly to it.
The alternative is to go to programmatic way, for instance a PL/SQL function, pipelined or returning a ref cursor.

[Updated on: Wed, 22 November 2023 15:58]

Report message to a moderator

Re: Transform row data into columns [message #689350 is a reply to message #689324] Mon, 27 November 2023 12:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Any feedback?

Report message to a moderator

Re: Transform row data into columns [message #689351 is a reply to message #689350] Mon, 27 November 2023 14:50 Go to previous messageGo to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
Hi Michael,

Thanks so much for this. I increased columns to around 15 as there is data till that point and testing various scenarios and will submit data to the end client and let you know if they have any questions.

Thank you.

Report message to a moderator

Re: Transform row data into columns [message #689352 is a reply to message #689351] Mon, 27 November 2023 17:48 Go to previous messageGo to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
Michael, if we have value like this how can we modify the code, if there is an underscore in notes column like ER_Sent instead of ER Sent and ER Received:

Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (264991,'2023-011660',8,'ER_Sent',to_date('31-AUG-24','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (264991,'2023-011660',9,'ER_Received',to_date('31-SEP-24','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (264991,'2023-011660',10,'ER_Sent',to_date('31-AUG-25','DD-MON-RR'));

Report message to a moderator

Re: Transform row data into columns [message #689353 is a reply to message #689352] Tue, 28 November 2023 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Just replace DECODE by CASE and REGEXP_LIKE (for instance to handle the case you could have more complex cases in the future):
SQL> with
  2    data as (
  3      select CID, CNUM, NOTES, ENTRY_DATE,
  4             count(case when regexp_like(NOTES,'ER[ _]Sent') then NOTES end)
  5               over (partition by CID, CNUM order by ENTRY_DATE)
  6               cntsent,
  7             count(case when regexp_like(NOTES,'ER[ _]Received') then NOTES end)
  8               over (partition by CID, CNUM order by ENTRY_DATE)
  9               cntrcv
 10         from a )
 11  select *
 12  from ( select CID, CNUM, ENTRY_DATE,
 13                2 * greatest(cntsent,cntrcv)
 14                - case when regexp_like(NOTES,'ER[ _]Sent') then 1 else 0 end
 15                 colnb
 16         from data )
 17  pivot (max(ENTRY_DATE)
 18         for colnb in (1 "ER Sent1", 2 "ER Received1", 3 "ER Sent2", 4 "ER Received2",
 19                       5 "ER Sent3", 6 "ER Received3", 7 "ER Sent4", 8 "ER Received4"))
 20  order by CID, CNUM
 21  /
       CID CNUM                 ER Sent1    ER Received ER Sent2    ER Received ER Sent3    ER Received ER Sent4    ER Received
---------- -------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
    264990 2023-010660          28-JUL-2023             18-AUG-2023 31-AUG-2023 11-SEP-2023             05-OCT-2023
    264991 2023-011660          18-JUL-2023 19-JUL-2023 18-AUG-2023 31-AUG-2023 31-AUG-2024 30-SEP-2024 31-AUG-2025
Use "[ _]" if you want to strictly restrict the cases to " " and "_" characters.
Change this expression to "." if you want to handle any character between "ER" and "Sent" or "Received" without changing the code.

Report message to a moderator

Re: Transform row data into columns [message #689355 is a reply to message #689353] Tue, 28 November 2023 09:24 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
Senior Member
 
with t as (
           select  cid,
                   cnum,
                   entry_date,
                   sum(
                       case
                         when regexp_like(notes,'^ER[ _]Sent$') then 2
                       end
                      )
                     over(partition by cid,cnum order by entry_date) -
                    case
                      when regexp_like(notes,'^ER[ _]Sent$') then 1
                      else 0
                    end n
             from  a
          )
select  *
  from  t
  pivot (
         max(entry_date)
         for n in (1 "ER Sent1", 2 "ER Received1", 3 "ER Sent2", 4 "ER Received2",
                   5 "ER Sent3", 6 "ER Received3", 7 "ER Sent4", 8 "ER Received4"
                  )
        )
  order by CID, CNUM
/
    CID CNUM        ER Sent1  ER Received1 ER Sent2  ER Received2 ER Sent3  ER Received3 ER Sent4  ER Received4
------- ----------- --------- ------------ --------- ------------ --------- ------------ --------- ------------
 264990 2023-010660 28-JUL-23              18-AUG-23 31-AUG-23    11-SEP-23              05-OCT-23
 264991 2023-011660 18-JUL-23 19-JUL-23    18-AUG-23 31-AUG-23

SQL>
SY.

Report message to a moderator

Re: Transform row data into columns [message #689356 is a reply to message #689353] Tue, 28 November 2023 12:02 Go to previous messageGo to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
There were cases where new texts came in, So i modified the code accordingly but it wasn't working due to same dates. Also changed partition order by from entry date to sort id, didnt work

Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (259,'9521',5,'3 failed',to_date('01-FEB-21','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (259,'9521',6,'3 failed',to_date('19-MAY-21','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (259,'9521',8,'3 failed',to_date('07-JUN-21','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (259,'9521',9,'3 failed',to_date('22-JUN-21','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (259,'9521',19,'ER Received',to_date('06-OCT-21','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (259,'9521',20,'ER Received',to_date('06-OCT-21','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (259,'9521',21,'ER Received(Incomplete)',to_date('06-OCT-21','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (259,'9521',22,'ER Received',to_date('12-OCT-21','DD-MON-RR'));


 with
      data as (
        select CID, CNUM, NOTES, ENTRY_DATE,
               count(case when  lower(NOTES) like '%sent%' or lower(NOTES) like '%fail%'  then NOTES end)
                over (partition by CID, CNUM order by sort_id)
                cntsent,
               count(case when lower(NOTES) like '%receiv%' then NOTES end)
                 over (partition by CID, CNUM order by sort_id)
                 cntrcv
         from a )
  select *
  from ( select CID, CNUM, ENTRY_DATE,
                2 * greatest(cntsent,cntrcv)
                - case when lower(NOTES) like '%sent%' or lower(NOTES) like '%fail%'  then 1 else 0 end
                 colnb
         from data )
  pivot (max(ENTRY_DATE)
         for colnb in (1 "ER Sent1", 2 "ER Received1", 3 "ER Sent2", 4 "ER Received2",
                       5 "ER Sent3", 6 "ER Received3", 7 "ER Sent4", 8 "ER Received4",
                       9 "ER Sent5", 10 "ER Received5", 11 "ER Sent6", 12 "ER Received6",
                       13 "ER Sent7", 14 "ER Received7", 15 "ER Sent8", 16 "ER Received8",
                       17 "ER Sent9", 18 "ER Received9", 19 "ER Sent10", 20 "ER Received10",
                       21 "ER Sent11", 22 "ER Received11", 23 "ER Sent12", 24 "ER Received12"))
  order by CID, CNUM

Report message to a moderator

Re: Transform row data into columns [message #689357 is a reply to message #689356] Tue, 28 November 2023 14:55 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
Senior Member
Well, you treat failed as sent, so where the "ER Sent5"/"ER Received5" through "ER Sent12"/"ER Received12" come from if cid 259 has 4 failed and 4 received? Anyway, post expected result and logic behind it.

SY.

Report message to a moderator

Re: Transform row data into columns [message #689358 is a reply to message #689356] Tue, 28 November 2023 16:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Completely changing the way to handle this for maybe (surely) not the most efficient one: recursive query:
SQL> with
  2    t as (
  3     select CID, CNUM, NOTES, ENTRY_DATE,
  4            row_number() over (partition by CID, CNUM order by SORT_ID) rn
  5     from a
  6    ),
  7    data (CID, CNUM, NOTES, ENTRY_DATE, rn, colnb) as (
  8      select CID, CNUM, NOTES, ENTRY_DATE, rn,
  9             case
 10               when lower(NOTES) like '%sent%' or lower(NOTES) like '%fail%' then 1
 11               when lower(NOTES) like '%receiv%' then 2
 12             end colnb
 13      from t
 14      where rn = 1
 15      union all
 16      select t.CID, t.CNUM, t.NOTES, t.ENTRY_DATE, t.rn,
 17             d.colnb
 18             + case
 19                 when    (   ( lower(t.NOTES) like '%sent%' or lower(t.NOTES) like '%fail%' )
 20                         and ( lower(d.NOTES) like '%sent%' or lower(d.NOTES) like '%fail%' ) )
 21                      or ( lower(t.NOTES) like '%receiv%' and lower(d.NOTES) like '%receiv%' )
 22                   then 2
 23                 else 1
 24               end
 25      from t, data d
 26      where t.CID = d.CID
 27        and t.CNUM = d.CNUM
 28        and t.rn = d.rn + 1
 29    )
 30  select *
 31  from (select CID, CNUM, ENTRY_DATE, colnb from data )
 32  pivot (max(ENTRY_DATE)
 33         for colnb in (1 "ER Sent1", 2 "ER Received1", 3 "ER Sent2", 4 "ER Received2",
 34                       5 "ER Sent3", 6 "ER Received3", 7 "ER Sent4", 8 "ER Received4",
 35                       9 "ER Sent5", 10 "ER Received6", 11 "ER Sent7", 12 "ER Received8",
 36                       13 "ER Sent9", 14 "ER Received9"))
 37  order by CID, CNUM
 38  /
       CID CNUM                 ER Sent1    ER Received ER Sent2    ER Received ER Sent3    ER Received ER Sent4    ER Received ER Sent5    ER Received ER Sent7    ER Received ER Sent9    ER Received
---------- -------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
       259 9521                 01-FEB-2021             19-MAY-2021             07-JUN-2021             22-JUN-2021 06-OCT-2021             06-OCT-2021             06-OCT-2021             12-OCT-2021
    264990 2023-010660          28-JUL-2023             18-AUG-2023 31-AUG-2023 11-SEP-2023             05-OCT-2023
    264991 2023-011660          18-JUL-2023 19-JUL-2023 18-AUG-2023 31-AUG-2023 31-AUG-2024 30-SEP-2024 31-AUG-2025

3 rows selected.
Rows are numbered in the SORT_ID order then I pick up the first one, determine its column (1 if sent/failed, 2 if received) then pick up the next row, if it is of the same type add 2 to colnb otherwise add 1, and so on with the next rows.

[Updated on: Tue, 28 November 2023 16:13]

Report message to a moderator

Re: Transform row data into columns [message #689359 is a reply to message #689357] Tue, 28 November 2023 16:32 Go to previous messageGo to next message
rajivn786
Messages: 161
Registered: January 2010
Senior Member
Thanks SY> Requirements keep changing based on the data I show to them. Basically they wanted Failed Sent messages as well and wanted to be treated as Sent but Received date should be populated only if they have sent.

op should be as attached.

  • Attachment: Untitled.jpg
    (Size: 51.64KB, Downloaded 863 times)

Report message to a moderator

Re: Transform row data into columns [message #689360 is a reply to message #689359] Wed, 29 November 2023 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

I don't understand your output: there is only one receive when you have 4 receive in your data!

[Updated on: Wed, 29 November 2023 00:38]

Report message to a moderator

Re: Transform row data into columns [message #689362 is a reply to message #689360] Wed, 29 November 2023 07:16 Go to previous messageGo to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
Thanks Michael and Sorry about that. In my previous message : Basically they wanted Failed Sent messages as well and wanted to be treated as Sent but Received date should be populated only if they have sent.

So since they wanted received only if they have sent, we shouldn't show the received. In our case the output is correct, but since from received date 5, they dont have sent dates, we need to hide them. Can we keep case statement on top of the query to show received only when they have sent/failed dates.

Report message to a moderator

Re: Transform row data into columns [message #689364 is a reply to message #689362] Wed, 29 November 2023 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
but Received date should be populated only if they have sent.

How do you know which "receive" is associated with which "sent" or "failed"?
In your data yo have 4 "failed" and 4 "received" so from your sentence no "received" should be displayed.
And if one is displayed why 6-OCT and not 12-OCT?
Now I'm completely lost in your specification.

[Edit: missing word]

[Updated on: Wed, 29 November 2023 10:22]

Report message to a moderator

Re: Transform row data into columns [message #689365 is a reply to message #689364] Wed, 29 November 2023 09:04 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
Senior Member
Quote:

Basically they wanted Failed Sent messages as well and wanted to be treated as Sent but Received date should be populated only if they have sent.
Then your sample (or my understanding) doesn't add up

Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (259,'9521',5,'3 failed',to_date('01-FEB-21','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (259,'9521',6,'3 failed',to_date('19-MAY-21','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (259,'9521',8,'3 failed',to_date('07-JUN-21','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (259,'9521',9,'3 failed',to_date('22-JUN-21','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (259,'9521',19,'ER Received',to_date('06-OCT-21','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (259,'9521',20,'ER Received',to_date('06-OCT-21','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (259,'9521',21,'ER Received(Incomplete)',to_date('06-OCT-21','DD-MON-RR'));
Insert into A (CID,CNUM,SORT_ID,NOTES,ENTRY_DATE) values (259,'9521',22,'ER Received',to_date('12-OCT-21','DD-MON-RR'));
My understanding is received is a reply to send. In the above sample all sends failed so, where these receives are from? As I said, post expected result and logic behind it.

SY.

Report message to a moderator

Re: Transform row data into columns [message #689367 is a reply to message #689364] Wed, 29 November 2023 10:08 Go to previous messageGo to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
I agree with you, but specs are not clear. Based on the data I asked, they are coming up with different ways.
But I sent them the data based on the latest code which is good as it displays all sent and received .

Report message to a moderator

Re: Transform row data into columns [message #689368 is a reply to message #689367] Wed, 29 November 2023 11:29 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
Senior Member
Well, if received can apply to failed then it isn't clear how to treat something like:

ER Sent     11/1/23
failed      11/3/23
ER Received 11/5/23
So does "ER Received 11/5/23" apply to "failed 11/3/23" or "ER Sent 11/1/23"? Some ground rules should be made before solution can be provided.

SY.

Report message to a moderator

Re: Transform row data into columns [message #689369 is a reply to message #689368] Wed, 29 November 2023 11:54 Go to previous messageGo to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
Sy, In these cases, always received applies to the latest sent/failed..

So it would be like this :


  ER Sent 1 -- 11/1/23
  ER Received 1 --
  ER Sent 2 -- 11/3/23
  ER received 2 -- 11/5/23

Report message to a moderator

Re: Transform row data into columns [message #689372 is a reply to message #689369] Wed, 29 November 2023 20:52 Go to previous message
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
Senior Member
OK, but then I still don't understand how did you come up with 12 send/receive. Also, why:

  ER Sent 1 -- 11/1/23
  ER Received 1 --
  ER Sent 2 -- 11/3/23
  ER received 2 -- 11/5/23
11/3/23 notes says "failed", so according to "but Received date should be populated only if they have sent" entry date for "ER received 2" should not be populated. Anyway:

with t as (
           select  cid,
                   cnum,
                   case
                     when lower(notes) like '%sent%' or lower(notes) like '%fail%' then entry_date
                     when last_value(case when lower(notes) not like '%received%' then lower(notes) end) ignore nulls over(partition by cid,cnum order by entry_date) like '%fail%' then null
                     else entry_date
                   end entry_date,
                   sum(
                       case
                         when lower(notes) like '%sent%' or lower(notes) like '%fail%' then 2
                       end
                      )
                     over(partition by cid,cnum order by entry_date) -
                    case
                      when lower(notes) like '%sent%' or lower(notes) like '%fail%' then 1
                      else 0
                    end n
             from  a
          )
select  *
  from  t
  pivot (
         max(entry_date)
         for n in (
                   1 "ER Sent1", 2 "ER Received1", 3 "ER Sent2", 4 "ER Received2",
                   5 "ER Sent3", 6 "ER Received3", 7 "ER Sent4", 8 "ER Received4"
                  )
        )
  order by cid,
           cnum
/

       CID CNUM                 ER Sent1  ER Received1 ER Sent2  ER Received2 ER Sent3  ER Received3 ER Sent4  ER Received4
---------- -------------------- --------- ------------ --------- ------------ --------- ------------ --------- ------------
       259 9521                 01-FEB-21              19-MAY-21              07-JUN-21              22-JUN-21
    264990 2023-010660          28-JUL-23              18-AUG-23 31-AUG-23    11-SEP-23              05-OCT-23
    264991 2023-011660          18-JUL-23 19-JUL-23    18-AUG-23 31-AUG-23

SQL>
SY.

[Updated on: Wed, 29 November 2023 20:53]

Report message to a moderator

Previous Topic: Sequentially update a column with a decrementing value
Next Topic: sql Qurey
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Feb 08 03:04:46 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.