I have this query: It's returning: I'm trying to make it like this, if anyone could help me, thanks Answer You can pivot using conditional aggregation: It is unclear what the specific values are that go into each column.

Get data horizontal in SQL

I have this query:

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

​x
 
SELECT X, COUNT(*) AS TotalCountFROM Table GROUP BY X, YORDER BY X asc​

It’s returning:

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

 
X | Count__________A | 10A | 11 A |  1B |  2B |  5B |  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

 
X | Count1 | Count2 | Count3A |  10    |   11   |  1B |  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;

 
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 tgroup by x;​

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

Advertisement

Answer