SQL: Shift columns value to left if a NULL value

Question

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:

Expected Output:

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

I am using snowflake db. But if I get the mysql or postgres equivalent I should be able to manage. — Mithun Manohar
– Mithun Manohar, Commented Jun 30, 2021 at 0:32
you need the result in select query or do you want to update the source table ? — Amit Verma
– Amit Verma, Commented Jun 30, 2021 at 1:44
Already answered here stackoverflow.com/questions/31874282/… — Amit Verma
– Amit Verma, Commented Jun 30, 2021 at 2:45
Does this answer your question? How to shift column values in MySQL? — FanoFN
– FanoFN, Commented Jun 30, 2021 at 6:15

Gordon Linoff · Accepted Answer · 2021-06-30 02:13:27Z

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.

Greg Pavlik · Accepted Answer · 2021-06-30 13:44:29Z

1

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;

edited Jun 30, 2021 at 13:44

answered Jun 30, 2021 at 3:29

Greg Pavlik

11.3k2 gold badges14 silver badges31 bronze badges

1 Comment

Mithun Manohar Over a year ago

Thanks, for the snowflake conversion! Seems like array_compact is zero-indexed. So to extract l1 we should use array_compact(array_construct(l1, l2, l3, l4)))[0] and so on..

Collectives™ on Stack Overflow

SQL: Shift columns value to left if a NULL value

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related