Home SQL, how to get the rows under the same Id if columnA in current row or columnB in following has data

Questions

SQL, how to get the rows under the same Id if columnA in current row or columnB in following has data

March 7, 2022

Imaging the original data is as the table below, I’d like to return the rows for Id 1 and 3. Because for the rows of Id 1, it has data in CodeA in one row, and another row has data for CodeB. For Id 3, it has data in CodeA and CodeB in one row.
The reason I don’t want Id 2 to return is because both rows for Id 2 only has data in CodeA, but not in CodeB.

Id	Group	CodeA	CodeB
1	1	a	null
1	1	null	a
1	2	null	a
2	1	a	null
2	2	a	null
3	1	a	b

The ideal result is:

Id	Group	CodeA	CodeB
1	1	a	null
1	1	null	a
1	2	null	a
3	1	a	b

>Solution :

We could use MAX() here as an analytic function:

WITH cte AS (
    SELECT *, MAX(CodeA) OVER (PARTITION BY Id) AS MaxCodeA,
              MAX(CodeB) OVER (PARTITION BY Id) AS MaxCodeB
    FROM yourTable
)

SELECT Id, [Group], CodeA, CodeB
FROM cte
WHERE MaxCodeA IS NOT NULL AND MaxCodeB IS NOT NULL;