0

I have a table with 3 columns. First column is an identifier, 2nd is value, 3rd is date. Each date has values for many identifiers. I would like to pivot this table in such a way that I have a row for each date and each column has an identifier.

Every answer I find uses CASE to pivot, however in my case I have many (50+) identifiers, and they would change as time goes on.

Here is the code I used, which did not work

with prep as (
select concat(ticker,' ',date) as papel , (pu+pu_juros) as PU, date from table where date >='2021-12-31' and ticker_bench = 'ticker') 

select * from prep
pivot (avg(PU) for papel in (select distinct papel from prep))

I tried a second version, defining a table with the values in which to pivot, this also did not work.

with prep_papel as (
select distinct concat(ticker,' ',date) as papel from table where date>='2021-12-31' and ticker_bench = 'ticker'
)

select * from (
select concat(ticker,' ',date) as papel , (pu+pu_juros) as PU, date from table where date >='2021-12-31' and ticker_bench = 'ticker')
as P

pivot (sum(p.PU) for papel in (select * from prep_papel) ) as pv1

Thank you

Osvaldo Assunção
  • 139
  • 1
  • 12
  • Dynamic Pivot -> https://stackoverflow.com/a/26297463/3251051. I just googled and found what you described above. It would have helped if you added `dynamic` in your question's title. – Zalom Feb 17 '23 at 11:28

0 Answers0