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.
Advertisement
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.