Skip to content
Advertisement

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.

I need to get the output as below

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:

Which for your sample data:

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.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement