SQL To move data when NOT NULL

Question

This is my first question here, so please be patient with me, so this is a challenge I got while working on a product data. Following is the data I have.

Column_A	Column_B	Column_C	Column_D	Column_E	Column_F
A1	Null	C1	D1	Null	F1
Null	Null	C2	Null	E2	F2
A3	B3	Null	D3	Null	F3
Null	Null	Null	Null	Null	F4

What I'm trying to achieve is to get the data formatted in such a way that it is plausible and convenient for the front-end developers to put it on a website. Basically, i have to move the data into other columns whenever there is a NULL. For example:

1. If the first, second and fifth columns are Null then the third , fourth and sixth column's data should be printed in the first three columns respectively
2. If all the first 5 columns are nulls, then the 6th column data should be printed in 1st column and so on

Expected Output :

Column_A	Column_B	Column_C	Column_D	Column_E	Column_F
A1	C1	D1	F1	Null	Null
C2	E2	F2	Null	Null	Null
A3	B3	D3	F3	Null	Null
F4	Null	Null	Null	Null	Null

Is this possible in SQL , if so, please help me with the approach.

Should I do it using case statements or is there a better approach?

Answer 1

You may try this:

• unpivot your data and keep the order for each column, i.e. Column_A order 1, Column_B order 2 and so on.
• use ROW_NUMBER() function with the unpivoted data where column value is not null to get a new order without nulls.
• pivot the data with conditional aggregation using the defined row number in the previous step to get the desired output.

---

with t as
(
  select id, Column_A as col, 1 as ord from table_name
  union all
  select id, Column_b, 2 from table_name
  union all
  select id, Column_c, 3 from table_name
  union all
  select id, Column_d, 4 from table_name
  union all
  select id, Column_e, 5 from table_name
  union all
  select id, Column_f, 6 from table_name
),
t2 as
(
  select *, 
    row_number() over (partition by id order by ord) rn
  from t where col is not null
)

select id,
       max(case when rn =1 then col end) Column_A,
       max(case when rn =2 then col end) Column_B,
       max(case when rn =3 then col end) Column_C,
       max(case when rn =4 then col end) Column_D,
       max(case when rn =5 then col end) Column_E,
       max(case when rn =6 then col end) Column_F
from t2 
group by id
order by id 

See a demo.