How to convert multiple columns into single column in snowflake table using query

Question

I have a table "TABLE1" like below

Date        src_counrty  src_State   dst_country    dst_state   Value
2022-02-09       a           d           x             y          1
2022-02-09       a           e           x             y          1
2022-02-09       c           f           x             y          1

I want to have output table as below using a select statement

Date         src    dst   Value
2022-02-09    a      x      1
2022-02-09    a      x      1
2022-02-09    c      x      1
2022-02-09    d      y      1
2022-02-09    e      y      1
2022-02-09    f      y      1
2022-02-09    a      y      1
2022-02-09    a      y      1
2022-02-09    c      y      1
2022-02-09    d      x      1
2022-02-09    e      x      1
2022-02-09    f      x      1

As seen in output table column src_counrty and src_State is merged as single column "src" and dst_country and dst_state as "dst" Is there any way to achieve this output using SQL query!

I have searched related topics on the internet and could not find any, so if anyone has any suggestion/solution for this it will be much helpful for me.. Thanks!!

Tim Biegeleisen · Accepted Answer · 2022-02-09 16:06:50Z

2

You may use a series of unions:

SELECT Date, src_counrty, dst_country, Value FROM TABLE1
UNION ALL
SELECT Date, src_State, dst_country, Value FROM TABLE1
UNION ALL
SELECT Date, src_counrty, dst_state, Value FROM TABLE1
UNION ALL
SELECT Date, src_State, dst_state, Value FROM TABLE1
ORDER BY Date;

If the order of the output matters beyond the date, state the logic and the above query can be updated.

answered Feb 9, 2022 at 16:06

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to convert multiple columns into single column in snowflake table using query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related