I have a table or data like this

This data has a same invoice number, so I want to show table only one column using case or pivot. The result that I want is like this

Can you help me about this ?

CodePudding user response：

Using PIVOT clause

WITH
  tbl AS    -- Sample Data
    (
      SELECT 'WSIV/H/02/22/00122' no_invoice, DATE '2022-08-04' paid_date, 50000 amount_apply FROM DUAL UNION ALL
      SELECT 'WSIV/H/02/22/00122' no_invoice, DATE '2022-08-06' paid_date, 60000 amount_apply FROM DUAL UNION ALL
      SELECT 'WSIV/H/02/22/00122' no_invoice, DATE '2022-08-07' paid_date, 70000 amount_apply FROM DUAL 
    )
SELECT
  no_invoice,
  "1_PAID_DATE" paid_date_1, "1_AMOUNT_APPLY" amount_apply_1,
  "2_PAID_DATE" paid_date_2, "2_AMOUNT_APPLY" amount_apply_2,
  "3_PAID_DATE" paid_date_3, "3_AMOUNT_APPLY" amount_apply_3,
  "4_PAID_DATE" paid_date_4, "4_AMOUNT_APPLY" amount_apply_4,
  NVL("1_AMOUNT_APPLY",0) NVL("2_AMOUNT_APPLY",0) NVL("3_AMOUNT_APPLY",0) NVL("4_AMOUNT_APPLY",0) NVL("5_AMOUNT_APPLY",0) total
FROM 
  ( SELECT 
      no_invoice, paid_date, amount_apply,
      LEAST(5, ROW_NUMBER() OVER (PARTITION BY NO_INVOICE ORDER BY paid_date)) bucket
    FROM tbl
  )
PIVOT
  ( ANY_VALUE(paid_date) paid_date, ANY_VALUE(amount_apply) amount_apply
    FOR bucket IN (1,2,3,4,5)
  )

Bucket 5 is used to trap all amount_apply's beyond the first 4 dates so they may be included in the total even though those amounts will not be shown in the columns (a catch all).

In Oracles prior to 19c use MAX rather than ANY_VALUE.

If you want the paid_date's to be aggregated so that all payments for an no_invoice on the same date appear just in one column, then change ROW_NUMBER to DENSE_RANK and ANY_VALUE(amount_apply) to SUM(amount_apply).

Result is as per OP. Thanks to @d-r for Sample Data.

CodePudding user response：

Using CASE expressions:

WITH
    tbl AS    -- Sample Data
        (
            Select 'WSIV/H/02/22/00122' "NO_INVOICE", To_Date('04.08-2022', 'dd.mm.yyyy') "PAID_DATE", 50000 "AMOUNT_APPLY" From Dual Union All
            Select 'WSIV/H/02/22/00122' "NO_INVOICE", To_Date('06.08-2022', 'dd.mm.yyyy') "PAID_DATE", 60000 "AMOUNT_APPLY" From Dual Union All
            Select 'WSIV/H/02/22/00122' "NO_INVOICE", To_Date('07.08-2022', 'dd.mm.yyyy') "PAID_DATE", 70000 "AMOUNT_APPLY" From Dual 
        )
SELECT
    NO_INVOICE,
    Max(PAID_DATE_1) "PAID_DATE_1", Max(AMOUNT_APPLY_1) "AMOUNT_APPLY_1",
    Max(PAID_DATE_2) "PAID_DATE_2", Max(AMOUNT_APPLY_2) "AMOUNT_APPLY_2",
    Max(PAID_DATE_3) "PAID_DATE_3", Max(AMOUNT_APPLY_3) "AMOUNT_APPLY_3",
    Max(PAID_DATE_4) "PAID_DATE_4", Max(AMOUNT_APPLY_4) "AMOUNT_APPLY_4",
    Nvl(Max(AMOUNT_APPLY_1), 0)   Nvl(Max(AMOUNT_APPLY_2), 0)   Nvl(Max(AMOUNT_APPLY_3), 0)   Nvl(Max(AMOUNT_APPLY_4), 0) "TOTAL"
FROM
    ( 
        Select
            NO_INVOICE,
            CASE WHEN ROW_NUMBER() OVER(Partition By NO_INVOICE Order By PAID_DATE) = 1 THEN PAID_DATE END "PAID_DATE_1", 
            CASE WHEN ROW_NUMBER() OVER(Partition By NO_INVOICE Order By PAID_DATE) = 2 THEN PAID_DATE END "PAID_DATE_2",
            CASE WHEN ROW_NUMBER() OVER(Partition By NO_INVOICE Order By PAID_DATE) = 3 THEN PAID_DATE END "PAID_DATE_3",
            CASE WHEN ROW_NUMBER() OVER(Partition By NO_INVOICE Order By PAID_DATE) = 4 THEN PAID_DATE END "PAID_DATE_4",
            CASE WHEN ROW_NUMBER() OVER(Partition By NO_INVOICE Order By PAID_DATE) = 1 THEN AMOUNT_APPLY END "AMOUNT_APPLY_1", 
            CASE WHEN ROW_NUMBER() OVER(Partition By NO_INVOICE Order By PAID_DATE) = 2 THEN AMOUNT_APPLY END "AMOUNT_APPLY_2",
            CASE WHEN ROW_NUMBER() OVER(Partition By NO_INVOICE Order By PAID_DATE) = 3 THEN AMOUNT_APPLY END "AMOUNT_APPLY_3",
            CASE WHEN ROW_NUMBER() OVER(Partition By NO_INVOICE Order By PAID_DATE) = 4 THEN AMOUNT_APPLY END "AMOUNT_APPLY_4"
        From
            tbl
    )
GROUP BY NO_INVOICE
ORDER BY NO_INVOICE

Result: