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 |
CodePudding user response:
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;