CODEWITHSUNDEEP
postgresql
Linu
Linu

Reputation: 597

Dynamic date pivot using CTE

I have a CTE which i get after lot of calculations, So the final CTE contains three columns namely, "Name", "Values" & "Dates". I have searched google and in all PIVOT the dates are hardcoded and i don't wan this dates to be hardcoded as the dates will be dynamically changed.

Any suggestions how i can get this result with a CTE and with dynamic dates pivot?

Version PostgreSQL 11 Sample input

Value.  Name          Date
"1" "Coenzym Q10"   "2020-06-29"
"1" "Coenzym Q10"   "2020-06-30"
"4" "Coenzym Q10"   "2020-07-01"
"1" "Coenzym Q10"   "2020-07-02"
"5" "Coenzym Q10"   "2020-07-03"
"1" "Coenzym Q10"   "2020-07-04"
"2" "D12"           "2020-07-01"
"4" "D12"           "2020-07-04"

Desired Output

"Name"        "2020-07-04".  "2020-07-03" "2020-07-02" "2020-07-01" "2020-06-30". "2020-06-29"
"Coenzym Q10".  "4"            "5"             "1"          "1"           "1"          "1"
"D12"           "4"                                         "2"

Upvotes: 0

Views: 60

Answers (1)

user330315
user330315

Reputation:

If you don't insist of having the dates as the column name (header), you could do something like this:

select "Name", 
       max("Value") filter (where date = current_date) as "Today",
       max("Value") filter (where date = current_date - 1) as "Today -1",
       max("Value") filter (where date = current_date - 2) as "Today -2",
       max("Value") filter (where date = current_date - 3) as "Today -3",
       max("Value") filter (where date = current_date - 4) as "Today -4",
       max("Value") filter (where date = current_date - 5) as "Today -5",
       max("Value") filter (where date = current_date - 6) as "Today -6",
       max("Value") filter (where date = current_date - 7) as "Today -7"
from the_table
group by "Name"
order by "Name;

Upvotes: 1

Related Questions