Merge two rows in SQL

Question

Assuming I have a table containing the following information   FK   Field1   Field2                       3    ABC       NULL  3     NULL    DEF   is there a way I can perform a select on the table to get the following  FK   Field1   Field2                       3    ABC      DEF   Thanks  Edit  Fix field2 name for clarity

User · Answer

There might be neater methods  but the following could be one approach   SELECT    t fk                           SELECT t1 Field1               FROM    table  t1               WHERE  t1 fk   t fk AND t1 Field1 IS NOT NULL              LIMIT  1             Field1                           SELECT t2 Field2              FROM    table  t2               WHERE  t2 fk   t fk AND t2 Field2 IS NOT NULL              LIMIT  1             Field2 FROM       table  t WHERE     t fk   3 GROUP BY  t fk    Test Case   CREATE TABLE  table   fk int  Field1 varchar 10   Field2 varchar 10     INSERT INTO  table  VALUES  3   ABC   NULL   INSERT INTO  table  VALUES  3  NULL   DEF    INSERT INTO  table  VALUES  4   GHI   NULL   INSERT INTO  table  VALUES  4  NULL   JKL    INSERT INTO  table  VALUES  5  NULL   MNO      Result    ------ -------- --------    fk     Field1   Field2    ------ -------- --------       3   ABC      DEF       ------ -------- --------  1 row in set  0 01 sec    Running the same query without the WHERE t fk   3 clause  it would return the following result-set    ------ -------- --------    fk     Field1   Field2    ------ -------- --------       3   ABC      DEF           4   GHI      JKL           5   NULL     MNO       ------ -------- --------  3 rows in set  0 01 sec

User · Answer

I had a similar problem  The difference was that I needed far more control over what I was returning so I ended up with an simple clear but rather long query  Here is a simplified version of it based on your example   select main id  Field1 Q Field1  Field2 Q Field2 from        select distinct id     from Table1  as main left outer join       select id  max Field1      from Table1     where Field1 is not null     group by id   as Field1 Q on main id   Field1 Q id left outer join       select id  max Field2      from Table1     where Field2 is not null     group by id   as Field2 Q on main id   Field2 Q id      The trick here is that the first select  main  selects the rows to display  Then you have one select per field  What is being joined on should be all of the same values returned by the  main  query   Be warned  those other queries need to return only one row per id or you will be ignoring data

User · Answer

There are a few ways depending on some data rules that you have not included  but here is one way using what you gave   SELECT     t1 Field1      t2 Field2 FROM Table1 t1     LEFT JOIN Table1 t2 ON t1 FK   t2 FK AND t2 Field1 IS NULL   Another way   SELECT     t1 Field1       SELECT Field2 FROM Table2 t2 WHERE t2 FK   t1 FK AND Field1 IS NULL  AS Field2 FROM Table1 t1

User · Answer

if one row has value in field1 column and other rows have null value then this Query might work   SELECT   FK    MAX Field1  as Field1    MAX Field2  as Field2 FROM    select FK ISNULL Field1     as Field1 ISNULL Field2     as Field2 from table1   tbl GROUP BY FK

User · Answer

SELECT Q FK        ISNULL T1 Field1  T2 Field2  AS Field FROM  SELECT FK FROM Table1         UNION       SELECT FK FROM Table2  AS Q LEFT JOIN Table1 AS T1 ON T1 FK   Q FK LEFT JOIN Table2 AS T2 ON T2 FK   Q FK   If there is one table  write Table1 instead of Table2

User · Answer

My case is I have a table like this   ---------------------------------------------  company name company ID CA            WA     ---------------------------------------------  Costco          1       NULL        2        ---------------------------------------------  Costco          1       3          Null      ---------------------------------------------   And I want it to be like below   ---------------------------------------------  company name company ID CA            WA     ---------------------------------------------  Costco          1       3           2        ---------------------------------------------   Most code is almost the same   SELECT     FK      MAX CA  AS CA      MAX WA  AS WA FROM     table1 GROUP BY company name company ID   The only difference is the group by  if you put two column names into it  you can group them in pairs

User · Answer

Aggregate functions may help you out here   Aggregate functions ignore NULLs  at least that s true on SQL Server  Oracle  and Jet Access   so you could use a query like this  tested on SQL Server Express 2008 R2    SELECT     FK      MAX Field1  AS Field1      MAX Field2  AS Field2 FROM     table1 GROUP BY     FK    I used MAX  but any aggregate which picks one value from among the GROUP BY rows should work   Test data   CREATE TABLE table1  FK int  Field1 varchar 10   Field2 varchar 10     INSERT INTO table1 VALUES  3   ABC   NULL   INSERT INTO table1 VALUES  3  NULL   DEF    INSERT INTO table1 VALUES  4   GHI   NULL   INSERT INTO table1 VALUES  4   JKL    MNO    INSERT INTO table1 VALUES  4  NULL   PQR      Results   FK  Field1  Field2 --  ------  ------ 3   ABC     DEF 4   JKL     PQR

[sql] Merge two rows in SQL

Examples related to sql

Examples related to join

Examples related to union