Get data horizontal in SQL

I have this query:

SELECT X, COUNT(*) AS TotalCount
FROM Table GROUP BY X, Y
ORDER BY X asc

It’s returning:

X | Count
__________
A | 10
A | 11 
A |  1
B |  2
B |  5
B |  6

I’m trying to make it like this, if anyone could help me, thanks

X | Count1 | Count2 | Count3
A |  10    |   11   |  1
B |  2     |   6    |  5

Answer

You can pivot using conditional aggregation:

select x,
       sum(case when y = @val1 then 1 else 0 end),
       sum(case when y = @val2 then 1 else 0 end),
       sum(case when y = @val3 then 1 else 0 end)
from t
group by x;

It is unclear what the specific values are that go into each column.