Variable value as column name in Snowflake-CodePudding

can I obtain in a query variable value as column name in Snowflake?

SET "CURRENT_YEAR"=YEAR(CURRENT_DATE());

SELECT SUM("AMOUNT") AS "$CURRENT_YEAR" (here I want the value 2021)

FROM "DB"."SCHEMA"."TABLE" 

WHERE YEAR("DATE") = $CURRENT_YEAR;

CodePudding user response：

Please try below:

create or replace table test (
    date date, 
    amount int
);

insert into test values 
('2021-01-01', 100),
('2022-01-01', 56),
('2022-02-01', 67),
('2021-05-01', 38),
('2023-01-01', 150),
('2021-01-06', 400),
('2021-07-11', 120)
;

SET "CURRENT_YEAR"=YEAR(CURRENT_DATE());

with year_tbl as (
    select year(date) as year, amount from test 
    where year = $CURRENT_YEAR
)
select *
from year_tbl
    pivot(sum(amount) for year in ($CURRENT_YEAR)) as yr
;


 ------ 
| 2021 |
|------|
|  658 |
 ------

If you want different years:

with year_tbl as (
    select year(date) as year, amount from test 
)
select *
from year_tbl
    pivot(sum(amount) for year in (2020, 2021, 2022, 2023)) as yr
;

 ------ ------ ------ ------ 
| 2020 | 2021 | 2022 | 2023 |
|------ ------ ------ ------|
| NULL |  658 |  123 |  150 |
 ------ ------ ------ ------

CodePudding user response：

did you mean something like this

create or replace table fld_year as 
(SELECT current_date() dt, 2021 as fld_year, 1 as AMT UNION ALL 
SELECT  current_date(),2021 as fld_year, 2 as r_num UNION ALL
SELECT current_date()- 900,2019 as fld_year, 3 as r_num UNION ALL
SELECT current_date()-400,2020 as fld_year, 4 as r_num );

SET "CURRENT_YEAR"=YEAR(CURRENT_DATE());

SELECT SUM(AMT) FROM fld_year  WHERE YEAR(dt) = $CURRENT_YEAR;

SELECT * FROM fld_year  WHERE YEAR(dt) = $CURRENT_YEAR;