How to club the non-null values in the rows in SQL

Question

I have the following situation:

COL_1   COL_2   COL_3   COL_4   COL_5   COL_6   COL_7
5678      ?        ?       ?       ?       ?    ?
?        6789      ?       ?       ?       ?    ?
?         ?      1223      ?       ?       ?    ?
?         ?        ?      1223     ?       ?    ?
?         ?        ?       ?       1223    ?    ?
?         ?        ?       ?       ?       1223 ?
?         ?        ?       ?       ?       ?    12823

And I want this:

COL_1   COL_2   COL_3   COL_4   COL_5   COL_6   COL_7
5678    6789    1223    1223    1223    1223    12823

I tried using COLAESCE () function but that doesn't seem to take in the column as a parameter.

Answer 1

You could just use MAX() on all columns like below if each row has a single value:

CREATE TABLE #data
(
    col1 INT,
    col2 INT,
    col3 INT
);

INSERT INTO #data
(
    col1,
    col2,
    col3
)
VALUES
(1, NULL, NULL),
(NULL, 2, NULL),
(NULL, NULL, 3);

SELECT MAX(d.col1) AS col1,
       MAX(d.col2) AS col2,
       MAX(d.col3) AS col3
FROM #data AS d;

DROP TABLE #data;