-1

Issue table:

col1   col2  col3 col4
----------------------
1      null  a    null
1      b     null null
1      null  null c
2      aa    null null
2      null  bb   null

Output needed

col1   col2  col3 col4
----------------------
1      b     a    c
2      aa    bb   null

Please help with a SQL query.

I want to remove the nulls and merge columns in a single row.

Improve this question
1
  • 3
    If you need to add something to your question, use the edit feature. Commented May 15, 2024 at 11:17

3 Answers 3

Reset to default
1

data

CREATE TABLE Issue (
   col1 INTEGER  NOT NULL  
  ,col2 VARCHAR(40)
  ,col3 VARCHAR(40)
  ,col4 VARCHAR(40)
);
INSERT INTO Issue 
(col1,col2,col3,col4) VALUES 
(1,NULL,'a',NULL),
(1,'b',NULL,NULL),
(1,NULL,NULL,'c'),
(2,'aa',NULL,NULL),
(2,NULL,'bb',NULL);

You just need group by

select 
col1,   
max(col2) col2,  
max(col3) col3,
max(col4) col4
from Issue 
group by col1

dbfiddle

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

i think the comment is trying to say he doesn't want collapse multiple non-null rows
@siggemannen What comment?
The deleted one :)
This is the answer if columns 2, 3, or 4 may never have more than one value for a given key (col1). If multiples might be present, the other answer that unpivots, numbers, and recombines the values (using conditional aggregation) is what you need.
1

One problem is that you need another column which returns the order of the values within the "col1", right now, there's no way to determine that, so i'm using @@SPID as sort, but it might not work in real scenarios. If you have some date or ID which can be used, you should replace the @@SPID with it.

The solution:

SELECT  col1
,   MAX(CASE WHEN type = 2 THEN v END) col2
,   MAX(CASE WHEN type = 3 THEN v END) col3
,   MAX(CASE WHEN type = 4 THEN v END) col4

FROM    (
    SELECT  row_number() OVER(partition BY col1 ORDER BY CASE WHEN col2 IS NULL THEN 1 ELSE 0 END, @@spid) AS row2
    ,   row_number() OVER(partition BY col1 ORDER BY CASE WHEN col3 IS NULL THEN 1 ELSE 0 END, @@spid) AS row3
    ,   row_number() OVER(partition BY col1 ORDER BY CASE WHEN col4 IS NULL THEN 1 ELSE 0 END, @@spid) AS row4
    ,   *
    FROM
    (
        VALUES  (1, NULL, N'a', NULL)
        ,   (1, N'b', NULL, NULL)
        ,   (1, NULL, NULL, N'c')
        ,   (2, N'aa', NULL, NULL)
        ,   (2, NULL, N'bb', NULL)
        ,   (3, N'aa', NULL, 'cc')
        ,   (3, 'xy', N'bb', NULL)
    ) t (col1,col2,col3,col4)
) x
CROSS APPLY (
    VALUES  (col2, row2, 2)
    ,   (col3, row3, 3)
    ,   (col4, row4, 4)
    ) up (v, row, type)
GROUP BY row, col1
HAVING count(v) > 0
  1. First, i create 3 row_numbers which sort each column into null and non null values. Here, you should substitute @@SPID with real column
  2. Then, I create an unpivot which generates a row for each column and type, this is so we can collapse the values
  3. Finally, a conditional aggregation collapses the values back per type, which generates col2,col3,col4.
  4. HAVING count(v) > 0 removes now completely empty rows from the equation.

Outputs:

col1 col2 col3 col4
1 b a c
2 aa bb NULL
3 xy bb cc
3 aa NULL NULL
Improve this answer

4 Comments

This is the answer if any of the columns 2, 3, or 4 may have more than one value for a given key (col1). If no multiples will ever occur, the simpler group-by plus max() answer will work.
Performing the unpivot before row_number() would simplify the logic slightly - fiddle.
That's much better @TN,you should post it as answer!
It's mostly your logic. You can add it to your answer if you like or just leave it as a comment, whatever you prefer.
0

To achieve the

SELECT 
    col1,
    COALESCE(MAX(col2), MAX(col3), MAX(col4)) AS col2,
    COALESCE(MAX(col3), MAX(col2), MAX(col4)) AS col3,
    COALESCE(MAX(col4), MAX(col2), MAX(col3)) AS col4
FROM 
    YourTable
GROUP BY 
    col1;

Result is by using the COALESCE() function along with aggregation functions like MAX() or MIN().

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.