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, Jun 30 '21 at 00:32
you need the result in select query or do you want to update the source table ? — Amit Verma, Jun 30 '21 at 01:44
Already answered here https://stackoverflow.com/questions/31874282/how-to-shift-column-values-in-mysql — Amit Verma, Jun 30 '21 at 02:45
Does this answer your question? [How to shift column values in MySQL?](https://stackoverflow.com/questions/31874282/how-to-shift-column-values-in-mysql) — FanoFN, Jun 30 '21 at 06:15

score 2 · Accepted Answer · answered Jun 30 '21 at 02:13

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 · Answer 2 · 2021-06-30T13:44:29.120

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 '21 at 13:44

answered Jun 30 '21 at 03:29

Greg Pavlik

10,089
2
12
29

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.. – Mithun Manohar Jun 30 '21 at 06:24

SQL: Shift columns value to left if a NULL value

2 Answers2