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;

Answer 2

SELECT   SUM( ISNULL(COL1,0)) AS COL_1
        ,SUM(ISNULL(COL2,0)) AS COL_2
        ,SUM(ISNULL(COL3,0)) AS COL_3
        ,SUM(ISNULL(COL4,0)) AS COL_4
        ,SUM(ISNULL(COL5,0)) AS COL_5
        ,SUM(ISNULL(COL6,0)) AS COL_6
        ,SUM(ISNULL(COL7,0)) AS COL_7
FROM YOUR_TABLE

Answer 3

Using COLAESCE will output all the results into one column. Use a parameter such as group by or where columns are not equal to "?"

Answer 4

SELECT
    (SELECT TOP(1) col1 FROM Table1 WHERE col1 IS NOT NULL ORDER BY SortCol) AS col1,
    (SELECT TOP(1) col2 FROM Table1 WHERE col2 IS NOT NULL ORDER BY SortCol) AS col2,
    (SELECT TOP(1) col3 FROM Table1 WHERE col3 IS NOT NULL ORDER BY SortCol) AS col3,
    (SELECT TOP(1) col4 FROM Table1 WHERE col4 IS NOT NULL ORDER BY SortCol) AS col4,
    (SELECT TOP(1) col5 FROM Table1 WHERE col5 IS NOT NULL ORDER BY SortCol) AS col5,
    (SELECT TOP(1) col6 FROM Table1 WHERE col6 IS NOT NULL ORDER BY SortCol) AS col6,
    (SELECT TOP(1) col7 FROM Table1 WHERE col7 IS NOT NULL ORDER BY SortCol) AS col7