Table 1:

How to join these two rows into a single row?

Required output:

Can someone please help me to join into a single row? Thank you...

CodePudding user response：

You may aggregate by the first 4 columns and take the max of the final 4 columns:

SELECT ID, Name, Class, Date,
       MAX(Intime) AS Intime, MAX(Outtime) AS Outtime, MAX(INAM) AS INAM,
       MAX(OUTPM) AS OUTPM
FROM yourTable
GROUP BY ID, Name, Class, Date;

CodePudding user response：

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