I have this table
id | cnt1 | cnt2 |
---|---|---|
7775 | 1 | |
7775 | 2 |
I would like to combine the rows so that it comes out to this
id | cnt1 | cnt2 |
---|---|---|
7775 | 1 | 2 |
I've tried a self join but it only appends the rows to the bottom. Any ideas? thank you!
CodePudding user response:
Depending on required logic you can use group by id
with corresponding aggregate function, for example max
(or sum
), which will ignore null values.
-- sample data
WITH dataset (id, cnt1, cnt2) AS (
VALUES (7775, 1, null),
(7775, null, 2)
)
-- query
select id,
max(cnt1) cnt1,
max(cnt2) cnt2
from dataset
group by id
Output:
id | cnt1 | cnt2 |
---|---|---|
7775 | 1 | 2 |
CodePudding user response:
use SubQuery
and join
as follows
SELECT T1.Id,
Cnt1,
Cnt2
FROM (SELECT Id,
Cnt1
FROM Table
WHERE Cnt1 IS NOT NULL) T1
JOIN (SELECT Id,
Cnt2
FROM Table
WHERE Cnt2 IS NOT NULL) T2
ON T1.Id = T2.Id