SQL Server query for grouping -

this question has answer here:

• sql server: examples of pivoting string data 6 answers

i have table 2 columns plateno , alerts.

table data

plateno    alerts           1   b         2   c         2           3   b         2           4           1 

now want result this:

alerts->   1  2  3  4 ---------------------------          2  0  1  1 b          0  2  0  0 c          0  1  0  0 

i mean 'a' has 2 alerts of type '1',1 alert of type '3' , '4'...and on..

here query trying with

select count(alert)  mytable  group plateno 

try :-

select plateno,[1],[2],[3],[4]  (  select plateno,alerts sample )p pivot  (  count(alerts)  alerts in ([1],[2],[3],[4]) )pvt 

demo here