SQL - Combine different rows into a single row with different ID-CodePudding

The table from different id needs to combine into a single row

         ----- ----- ----- ----- 
        | Id  | Col1| Col2| Col3|
         ----- ----- ----- ----- 
        | 1   |     |   1 |     |
        | 2   |     |   2 |     |
        | 3   |   3 |     |     |
        | 4   |   4 |     |     |
        | 5   |     |     |   5 |
        | 6   |     |     |   6 |
         ----- ----- ----- -----

What I want to achieve in the same table.

         ----- ----- ----- ----- 
        | Id  | Col1| Col2| Col3|
         ----- ----- ----- ----- 
        | 1   |   3 |   1 |   5 |
        | 2   |   4 |   2 |   6 |
         ----- ----- ----- -----

What I had tried to aggregate the rows from using different id

Select ID
      ,ID1 = max(Col1)
      ,ID2 = max(Col2)
      ,ID3 = max(Col3)
 From  Tbl
 Group By ID

CodePudding user response：

Your data

create table test(
   Id   INTEGER  NOT NULL 
  ,Col1 INTEGER  
  ,Col2 INTEGER
  ,Col3 INTEGER
);
INSERT INTO test
(Id,Col1,Col2,Col3)
VALUES
(1,NULL,1,NULL),
(2,NULL,2,NULL),
(3,3,NULL,NULL),
(4,4,NULL,NULL),
(5,NULL,NULL,5),
(6,NULL,NULL,6);

UNION the result of Max function with Min function

SELECT 1         AS ID,
       Min(col1) Col1,
       Min(col2) Col2,
       Min(col3) Col3
FROM   test
UNION
SELECT 2         AS ID,
       Max(col1) Col1,
       Max(col2) Col2,
       Max(col3) Col3
FROM   test

In addition you can use multiple join and Subbquery and Where condition to

SELECT a.id1 AS id,
       b.col1,
       a.col2,
       c.col3
FROM  (SELECT Row_number()
                OVER (
                  ORDER BY id ) id1,
              *
       FROM   Test
       WHERE  col2 IS NOT NULL) a
      JOIN (SELECT Row_number()
                     OVER (
                       ORDER BY id ) id1,
                   *
            FROM   Test
            WHERE  col1 IS NOT NULL) b
        ON a.id1 = b.id1
      JOIN(SELECT Row_number()
                    OVER (
                      ORDER BY id ) id1,
                  *
           FROM   Test
           WHERE  col3 IS NOT NULL) c
        ON a.id1 = c.id1

dbfiddle

CodePudding user response：

I do not know what you wanted from this result but you can try a query like this:

Select ID
      ,(select max(Col1) from tbl) as ID1
      ,(select max(Col2) from tbl) as ID2
      ,(select max(Col3) from tbl) as ID3
 From  Tbl
 Group By ID