Skip to Main Content
  • Questions
  • Combine create and insert with if-then or case-when in sql?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Stefana.

Asked: March 29, 2020 - 12:54 am UTC

Answered by: Connor McDonald - Last updated: March 30, 2020 - 3:17 pm UTC

Category: SQL - Version: oracle sql developer

Viewed 100+ times

You Asked

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.

and we said...

and you rated our response

  (1 rating)

Reviews

SQL

March 30, 2020 - 7:20 am UTC

Reviewer: Lidija

Thank you for your answer for the question. It was pivoting that you try, but I found this answer that worked for me:

MAX(CASE WHEN P.YEAR = 2015 THEN P.AMOUNT END) AS AMOUNT15,
...
FROM
SOMETABLE P
GROUP BY P.ID

Chris Saxon

Followup  

March 30, 2020 - 3:17 pm UTC

Glad you got it working

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.