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.

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 :

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?

CodePudding user response：

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.

CodePudding user response：

Following query will do step for step exactly the logic you described and produce exactly the correct outcome. There will be other ways, too, I just played around to have some fun ;)

SELECT 
columnA,
CASE WHEN columnA = columnB THEN NULL 
ELSE columnB END AS columnB,
CASE WHEN columnC IN (columnA,columnB) THEN NULL 
ELSE columnC END AS columnC,
CASE WHEN columnD IN (columnA,columnB,columnC) THEN NULL 
ELSE columnD END AS columnD,
CASE WHEN columnE IN (columnA,columnB,columnC,columnD) THEN NULL 
ELSE columnE END AS columnE,
CASE WHEN columnF IN (columnA,columnB,columnC,columnD,columnE) THEN NULL 
ELSE columnF END AS columnF
FROM
(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(sub,',',1 ), ',', -1) AS columnA, 
SUBSTRING_INDEX(SUBSTRING_INDEX(sub,',',2 ), ',', -1) AS columnB,
SUBSTRING_INDEX(SUBSTRING_INDEX(sub,',',3 ), ',', -1) AS columnC,
SUBSTRING_INDEX(SUBSTRING_INDEX(sub,',',4 ), ',', -1) AS columnD,
SUBSTRING_INDEX(SUBSTRING_INDEX(sub,',',5 ), ',', -1) AS columnE,
SUBSTRING_INDEX(SUBSTRING_INDEX(sub,',',6 ), ',', -1) AS columnF
FROM
(SELECT 
CONCAT(
CASE WHEN columnA IS NULL THEN '' ELSE CONCAT(columnA,',') END,
CASE WHEN columnB IS NULL THEN '' ELSE CONCAT(columnB,',') END,
CASE WHEN columnC IS NULL THEN '' ELSE CONCAT(columnC,',') END,
CASE WHEN columnD IS NULL THEN '' ELSE CONCAT(columnD,',') END,
CASE WHEN columnE IS NULL THEN '' ELSE CONCAT(columnE,',') END,
CASE WHEN columnF IS NULL THEN '' ELSE columnF END)
AS sub
FROM yourtable) x)y;

See here the outcome is correct: db<>fiddle

Side note: The clue of this query is - beside the exciting syntax - that for example the value of columnD in the third row of the result will be null if you change the input value from F3 to D3. You didn't tell us whether this is intended, but I like this functionality.