Get column names as values in a new column in Snowflake

Question

I have sample data with following columns;

total_container_cnt | forty_ft_container_cnt | twenty_ft_container_cnt | fifty_three_ft_container_cnt

3                   |      NULL              |    3                    |   NULL
2                   |       2                |      NULL               |   NULL

The goal is to get following output;

Expected output

total_container_cnt | forty_ft_container_cnt | twenty_ft_container_cnt | fifty_three_ft_container_cnt | NEW_COLUMN

3                   |      NULL              |    3                    |   NULL      | 3*twenty_ft_container_cnt
2                   |       2                |      NULL               |   NULL      | 2* forty_ft_container_cnt

Here is my attempt at this ,

select temp.*,
       case when forty_ft_container_cnt is not null then concat('total_container_cnt','*','forty_ft_container_cnt)
       when twenty_ft_container_cnt is not null then concat('total_container_cnt','*','twenty_ft_container_cnt')
       when fifty_three_ft_container_cnt is not null then concat('total_container_cnt','*','fifty_three_ft_container_cnt)
 end as new_column

from temp
      

This approach works but involve multiple case when statements. Is it possible to get some help here to have a simpler/shorter solution?

Answer 1

You need UNPIVOT. In the example below, an ID column was added for the join.

create or replace temporary table t0(
    id integer,
    total_container_cnt integer,
    forty_ft_container_cnt integer,
    twenty_ft_container_cnt integer,
    fifty_three_ft_container_cnt integer
) as
select * from values
    (101, 3, NULL, 3, NULL),
    (102, 2, 2, NULL, NULL);

with t1 as (
    select * from t0
    unpivot (
            count for container in (forty_ft_container_cnt, twenty_ft_container_cnt, fifty_three_ft_container_cnt)
        )
)
select
    t0.*,
    t1.count || '*' || container as new_column
from t0
join t1
on t0.id = t1.id
    and t1.count > 0;

ID	TOTAL_CONTAINER_CNT	FORTY_FT_CONTAINER_CNT	TWENTY_FT_CONTAINER_CNT	FIFTY_THREE_FT_CONTAINER_CNT	NEW_COLUMN
101	3	null	3	null	3*TWENTY_FT_CONTAINER_CNT
102	2	2	null	null	2*FORTY_FT_CONTAINER_CNT