Hello people,
I would like to ask for your help with a simple issue. I am srry if this is too common, but for me it is not easy at all:
I have a table like this - p.sometable:
ID AMOUNT YEAR
cusomter1 42476281.61 2015
cusomter1 111871028.7 2018
cusomter1 77107034.06 2016
cusomter1 3633451.59 2017
cusomter1 122988385.5 2019
cusomter1 4565.96 2020
customer2 28934 2018
customer2 227144 2016
customer2 8603908.77 2019
customer2 7557336.03 2017
customer2 11361602.78 2015
and I need table like this (transposing, pivoting, whatever..) - p.mytable:
ID AMOUNT15 AMOUNT16 AMOUNT17 AMOUNT18 AMOUNT19 AMOUNT20
customer1 42476281.61 7710703 3633451.59 111871028.7 122988385.5 4565.96
customer2 11361602.78 227144 7557336.03 28934 8603908.77 0
This is a code which is not working:
CREATE TABLE p.mytable
(
ID VARCHAR2(255 BYTE),
AMOUNT15 NUMBER,
AMOUNT16 NUMBER,
AMOUNT17 NUMBER,
AMOUNT18 NUMBER,
AMOUNT19 NUMBER,
AMOUNT20 NUMBER
)
INSERT INTO p.mytable
(
ID VARCHAR2(255 BYTE),
AMOUNT15,
AMOUNT16,
AMOUNT17,
AMOUNT18,
AMOUNT19,
AMOUNT20
)
SELECT
ID AS ID,
CASE YEAR
WHEN 2015 THEN
(AMOUNT15 = AMOUNT)
WHEN 2016 THEN
(AMOUNT16 = AMOUNT)
WHEN 2017 THEN
(AMOUNT17 = AMOUNT)
WHEN 2018 THEN
(AMOUNT18 = AMOUNT)
WHEN 2019 THEN
(AMOUNT19 = AMOUNTS)
WHEN 2020 THEN
(AMOUNT20 = AMOUNTS)
END
FROM
p.sometable
Thank you very much in advance.