SQL: Shift columns value to left if a NULL value

I have a table that looks like below, with possible nulls in initial columns. I am looking to shift the columns to left in case a null is found so that all the categories align together.

Input:
SQL: Shift columns value to left if a NULL value

Expected Output:
SQL: Shift columns value to left if a NULL value

Below are the DDL statements:

CREATE TABLE CATEGORIES
(L1 varchar(255),
 L2 varchar(255),
 L3 varchar(255),
 L4 varchar(255)
);

Insert records:

INSERT INTO CATEGORIES
(L1, L2, L3, l4)
VALUES
('Womens Clothing' , 'Womens Accessories', 'View all Accessories', null );

INSERT INTO CATEGORIES
(L1, L2, L3, l4)
VALUES
(null , 'Womens Clothing', 'Womens Accessories', 'Bags');


INSERT INTO CATEGORIES
(L1, L2, L3, l4)
VALUES
(null , null, 'Mens Clothing', 'Shirts' );

I am quite new to SQL and quite not able to wrap my head around how to solve this

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You can use a lateral join, filter, and reaggregate:

select *
from categories c cross join lateral
     (select max(case when seqnum = 1 then val end) as l1,
             max(case when seqnum = 2 then val end) as l2,
             max(case when seqnum = 3 then val end) as l3,
             max(case when seqnum = 4 then val end) as l4
      from (select v.*, row_number() over (order by ord) as seqnum
            from (values (1, l1), (2, l2), (3, l3), (4, L4)
                 ) v(ord, val)
            where val is not null
           ) v
     ) v;

Here is a db<>fiddle using Postgres.

Or using arrays:

select (array_remove(array[l1, l2, l3, l4], NULL))[1] as l1,
       (array_remove(array[l1, l2, l3, l4], NULL))[2] as l2,
       (array_remove(array[l1, l2, l3, l4], NULL))[3] as l3,
       (array_remove(array[l1, l2, l3, l4], NULL))[4] as l4
from categories c;

I’m not sure which of these is easier for you to translate into Snowflake.

Method 2

Translating Gordon Linoff’s second example to Snowflake works.

select (array_compact(array_construct(l1, l2, l3, l4)))[0]::varchar as l1,
       (array_compact(array_construct(l1, l2, l3, l4)))[1]::varchar as l2,
       (array_compact(array_construct(l1, l2, l3, l4)))[2]::varchar as l3,
       (array_compact(array_construct(l1, l2, l3, l4)))[3]::varchar as l4
from categories c;


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x