Urgent help needed with groupby and distinct count

[SINGHTX9]

Hi All

Please, can anyone really help me to solve my issue. I have already posted my issue few time but no one could give me a solution.Actual dataMatrix data

I have the records for a laboratory receiving two type of tests: CC or IA.

So each Accession ID can have:

• CC tests only
• IA tests only
• CC IA tests both 
• No tests

So i want to unpivot the test type column but doing transpose doesnt help me.

 i tried to create a matrix table (Attached pic) so as you can see it returns the count of "CC" and "IA" tests, but my end goal is to 

get a tabel with 

Accession ID     CC    IA

and the columns CC and IA should have only binary number i.e if that accession ID has "CC" tests then return 1 else 0. Similarly with IA tests. I dont care about the number, all i care is whether the accession ID has the "CC" test ordered or not.

End result

[SINGHTX9 · Accepted solution]

Also I had alreday creating the two columns CC and IA using If statements as you had suggested above but this was the result i got. As expected it was missing IA and CC common accesion id and returning it as zero