How to Pivot row (Dates of the month ) into columns in sql ? ( without giving the date of the month “for in” )

I have a data table which data as below & every prod_id have an amount daily for the month of Feb.

DATE        PROD_ID  AMOUNT   PRO_CAT  PRO_NAME  ID     OFFI

01-FEB-20   AA96RV9 979847.34   3110    A       7854     123
01-FEB-20   AAL3SMJ 590447.37   3110    A       5847    456
01-FEB-20   AAD1VK5 492104.45   3110    A       985     006
01-FEB-20   AA4C0RN 2327107.16  3110    B       784     008
01-FEB-20   AA1W4BY 40354.9     3110    B       987     155
01-FEB-20   AAVM422 587447.54   3110    A       1674    122
01-FEB-20   AAXS0DD 286987.72   3110    B       8030    103
01-FEB-20   AA6TJK5 269847.82   3110    C       489     176

I need to get the output as below

PROD_ID PRO_CAT PRO_NAME id OFFI    01-Feb-20   02-Feb-20   03-Feb-20   04-Feb-20   05-Feb-20   06-Feb-20

AA96RV9 3110    A   7854    123    979847.34    977847.34   847.34  27869   979847.34   979847.34
AAL3SMJ 3110    A   5847    456    590447.37    590447.37   590447.37   590447.37   590447.37   590447.37
AAD1VK5 3110    A   985     006 492104.45   492104.45   492104.45   492104.45   492104.45   492104.45
AA4C0RN 3110    B   784     008 2327107.16  2327107.16  2327107.16  2327107.16  2327107.16  2327107.16
AA1W4BY 3110    B   987     155   40354.9   40354.9 140354.9    140354.9    140354.9    140354.9
AAVM422 3110    A   1674    122 587447.54   587447.54   587447.54   587447.54   587447.54   587447.54
AAXS0DD 3110    B   8030    103 286987.72   286987.72   286987.72   286987.72   286987.72   286987.72
AA6TJK5 3110    C   489    176  269847.82   269847.82   269847.82   269847.82   269847.82   269847.82

SELECT * FROM
(
  SELECT PROD_ID    , PRO_CAT   ,PRO_NAME ,id   ,OFFI
  FROM CHECK_TABLE
)
PIVOT
(
  SUM(amount)
  FOR DATE IN (DATE)
)
ORDER BY PROD_ID    , PRO_CAT   ,PRO_NAME ,id   ,OFFI;

Please help ,getting the error of missing right parenthesis

FOR DATE IN (DATE) without giving the date of the month (specific as FOR DATE IN (’01-FEB-20′,….) ) because i need to run this every month.

Answer

You can’t have the column names dynamically set to the full date value in a simple SQL statement (or if you did then you would need to generate the query in PL/SQL using dynamic SQL and run it using EXECUTE IMMEDIATE or something similar).

Instead, assume a maximum of 31 days-per-month and extract the day-of-the-month and use that to pivot on:

SELECT *
FROM   (
  SELECT PROD_ID,
         AMOUNT,
         PRO_CAT,
         PRO_NAME,
         id,
         OFFI,
         EXTRACT( DAY FROM "DATE" ) As day
  FROM   CHECK_TABLE
  WHERE  "DATE" >= DATE '2020-02-01'
  AND    "DATE" <  ADD_MONTHS( DATE '2020-02-01', 1 )
)
PIVOT
(
  SUM(amount)
  FOR Day IN (
    1 AS Day1,
    2 As Day2,
    3 As Day3,
    --etc.
    29 AS Day29,
    30 AS Day30,
    31 AS Day31
  )
)
ORDER BY
       PROD_ID,
       PRO_CAT,
       PRO_NAME,
       id,
       OFFI;

Which for your sample data:

CREATE TABLE check_table ( "DATE", PROD_ID, AMOUNT, PRO_CAT, PRO_NAME, ID, OFFI ) AS
SELECT DATE '2020-02-01', 'AA96RV9',  979847.34, 3110, 'A', 7854, '123' FROM DUAL UNION ALL
SELECT DATE '2020-02-01', 'AAL3SMJ',  590447.37, 3110, 'A', 5847, '456' FROM DUAL UNION ALL
SELECT DATE '2020-02-01', 'AAD1VK5',  492104.45, 3110, 'A',  985, '006' FROM DUAL UNION ALL
SELECT DATE '2020-02-01', 'AA4C0RN', 2327107.16, 3110, 'B',  784, '008' FROM DUAL UNION ALL
SELECT DATE '2020-02-01', 'AA1W4BY',   40354.90, 3110, 'B',  987, '155' FROM DUAL UNION ALL
SELECT DATE '2020-02-01', 'AAVM422',  587447.54, 3110, 'A', 1674, '122' FROM DUAL UNION ALL
SELECT DATE '2020-02-01', 'AAXS0DD',  286987.72, 3110, 'B', 8030, '103' FROM DUAL UNION ALL
SELECT DATE '2020-02-01', 'AA6TJK5',  269847.82, 3110, 'C',  489, '176' FROM DUAL

Gives the output:

PROD_ID | PRO_CAT | PRO_NAME |   ID | OFFI |       DAY1 | DAY2 | DAY3 | DAY29 | DAY30 | DAY31
:------ | ------: | :------- | ---: | :--- | ---------: | ---: | ---: | ----: | ----: | ----:
AA1W4BY |    3110 | B        |  987 | 155  |    40354.9 | null | null |  null |  null |  null
AA4C0RN |    3110 | B        |  784 | 008  | 2327107.16 | null | null |  null |  null |  null
AA6TJK5 |    3110 | C        |  489 | 176  |  269847.82 | null | null |  null |  null |  null
AA96RV9 |    3110 | A        | 7854 | 123  |  979847.34 | null | null |  null |  null |  null
AAD1VK5 |    3110 | A        |  985 | 006  |  492104.45 | null | null |  null |  null |  null
AAL3SMJ |    3110 | A        | 5847 | 456  |  590447.37 | null | null |  null |  null |  null
AAVM422 |    3110 | A        | 1674 | 122  |  587447.54 | null | null |  null |  null |  null
AAXS0DD |    3110 | B        | 8030 | 103  |  286987.72 | null | null |  null |  null |  null

(Your sample data set has no values for later days of the month so they are all NULL, I’m assuming your full data set would have these values.)

db<>fiddle here

If you are using a middle-tier (Java, C#, PHP, etc.) to run the query then you can dynamically re-name the columns in that but don’t try to do it dynamically in SQL.