Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

CREATE TABLE


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' );

INSERT 0 1



INSERT 0 1



INSERT 0 1


select * from categories


l1
l2
l3
l4


Womens Clothing
Womens Accessories
View all Accessories
null

null
Womens Clothing
Womens Accessories
Bags

null
null
Mens Clothing
Shirts



SELECT 3


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


l1
l2
l3
l4
l1
l2
l3
l4


Womens Clothing
Womens Accessories
View all Accessories
null
Womens Clothing
Womens Accessories
View all Accessories
null

null
Womens Clothing
Womens Accessories
Bags
Womens Clothing
Womens Accessories
Bags
null

null
null
Mens Clothing
Shirts
Mens Clothing
Shirts
null
null



SELECT 3


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


l1
l2
l3
l4


Womens Clothing
Womens Accessories
View all Accessories
null

Womens Clothing
Womens Accessories
Bags
null

Mens Clothing
Shirts
null
null



SELECT 3

l1	l2	l3	l4
Womens Clothing	Womens Accessories	View all Accessories	null
null	Womens Clothing	Womens Accessories	Bags
null	null	Mens Clothing	Shirts