T-SQL – subqueries on rows grouped by ID to create a summary table

I have a table “MyTable” with an id and two int fields “A” and “B”:

MyTable
+-------+-----+-----+
|   ID  |  A  |  B  |
+-------+-----+-----+
|    99 |  0  |  1  |
|    99 |  0  |  1  |
|    99 |  0  |  0  |
|    99 |  1  |  1  |
|    99 |  0  |  1  |
|   100 |  1  |  0  |
|   100 |  0  |  0  |
|   100 |  0  |  0  |
|   444 |  1  |  0  |
|    88 |  0  |  0  |
|    88 |  0  |  0  |
|    88 |  0  |  1  |
+-------+-----+-----+

I’d like a T-SQL query to return a single row for each distinct id, which contains:

each distinct ID
whether there exists a row for that ID with a non-zero value for “a”
whether there exists a row for that ID with a non-zero value for “b”

like so:

+-------+-----+-----+
|   ID  |  A  |  B  |
+-------+-----+-----+
|    99 |  1  |  1  |
|   100 |  1  |  0  |
|   444 |  1  |  0  |
|    88 |  0  |  1  |
+-------+-----+-----+

I can GROUP BY the ID, but I don’t know how to create the joins or subqueries on each group to get the desired result.

Answer

select id, max(case when A<>0 then 1 else 0 end)A, max(case when B<>0 then 1 else 0 end)B
from mytable 
group by id

Or you can just use since your value is 1 and 0. But if value is other than that please use first query.

select id, max(A)A, max(B)B
from mytable 
group by id

Advertisement

Answer