Unpivot in postgres with a column created in the same query-CodePudding

I am trying to unpivot a table with PostgreSQL as described here.

My problem is that I am creating a new column in my query which I want to use in my cross join lateral statement (which results in an SQL error because the original table does not have this column).

ORIGINAL QUESTION:

select
  "Name", 
  case
    when "Year"='2020' then "Date"
  end as "Baseline"
from "test_table"

EDIT: I am using the example from the referred StackOverflow question:

create table customer_turnover
(
  customer_id   integer,
  q1            integer,
  q2            integer,
  q3            integer,
  q4            integer
);

INSERT INTO customer_turnover  VALUES
    (1, 100, 210, 203, 304);
INSERT INTO customer_turnover  VALUES
    (2, 150, 118, 422, 257);
INSERT INTO customer_turnover  VALUES
    (3, 220, 311, 271, 269);
INSERT INTO customer_turnover  VALUES
    (3, 320, 211, 171, 269);

select * from customer_turnover;

creates the following output

customer_id   q1   q2  q3  q4
1             100  210 203 304
2             150  118 422 257
3             220  311 271 269
3             320  211 171 269

(I used the customer_id 3 twice because this column is not unique)

Essentially, what I would like to do is the following: I would like to calculate a new column qsum:

select customer_id, q1, q2, q3, q4, 
  q1 q2 q3 q4 as qsum
from customer_turnover

and use this additional column in my unpivoting statement to produce the following output:

customer_id turnover quarter
1           100      Q1
1           210      Q2
1           203      Q3
1           304      Q4
1           817      qsum
2           150      Q1
2           118      Q2
2           422      Q3
2           257      Q4
2           947      qsum
3           220      Q1
3           311      Q2
3           271      Q3
3           269      Q4
3           1071     qsum
3           320      Q1
3           211      Q2
3           171      Q3
3           269      Q4
3           971      qsum

As I do not want to have qsum in my final output, I understand that I cannot use it in my select statement, but even if I would use it like this

select customer_id, t.*, q1, q2, q3, q4, 
  q1 q2 q3 q4 as qsum
from customer_turnover c
  cross join lateral (
     values 
       (c.q1, 'Q1'),
       (c.q2, 'Q2'),
       (c.q3, 'Q3'),
       (c.q4, 'Q4'),
       (c.qsum, 'Qsum')
  ) as t(turnover, quarter)

I receive the following SQL error: ERROR: column c.qsum does not exist

How can I produce my desired output?

CodePudding user response：

Not sure to well understand your issue, maybe a subquery can help :

select s.baseline
from
   ( select
       "Name", 
       case
         when "Year"='2020' then "Date"
       end as "Baseline"
      from "test_table"
   ) AS s