Imagine this data.
| Id | Type |
|---|---|
| 1 | A |
| 1 | B |
| 1 | B |
| 2 | A |
| 3 | B |
I want to select table and ad two columns turning it to this. How can i do it? (In teradata)
| Id | Type | Id with both A B | Id with only A |
|---|---|---|---|
| 1 | A | 1 | 0 |
| 1 | B | 1 | 0 |
| 1 | B | 1 | 0 |
| 2 | A | 0 | 1 |
| 3 | B | 0 | 0 |
CodePudding user response：
I'm not familiar with teradata but in standard SQL next query should be working:
SELECT
T.*,
CASE WHEN Cnt = 2 THEN 1 ELSE 0 END AS BOTH_TYPES_PRESENT,
CASE WHEN Cnt = 1 AND Type = 'A' THEN 1 ELSE 0 END AS ONLY_A_PRESENT
FROM T
LEFT JOIN (
SELECT Id, COUNT(DISTINCT Type) Cnt FROM T WHERE Type IN ('A', 'B') GROUP BY Id
) CNT ON T.Id = CNT.Id;