SQL Server query for grouping

Question

I have a table with 2 columns plateno and alerts.

table Data

plateno    alerts
  A         1
  B         2
  C         2
  A         3
  B         2
  A         4
  A         1

Now I want to get the result like this:

alerts->   1  2  3  4
---------------------------
A          2  0  1  1
B          0  2  0  0
C          0  1  0  0

I mean 'A' has two alerts of type '1',1 alert of type '3' and '4'...and so on..

Here is my query I am trying with

Select count(alert) 
from mytable 
group by plateno

Where's the SQL query you're trying to get working? – Matt Ball May 06 '13 at 04:28 — Matt Ball, May 06 '13 at 04:28

score 2 · Accepted Answer · answered May 06 '13 at 04:31

2

Try this :-

Select plateno,[1],[2],[3],[4]
from 
(
 Select plateno,alerts from Sample
)p
pivot 
(
 count(alerts)
 for alerts in ([1],[2],[3],[4])
)pvt

Demo Here

answered May 06 '13 at 04:31

praveen

12,083
1
41
49

score 2 · Answer 2 · answered May 06 '13 at 04:36

If you have unknown number of values for the column Alert, a dynamic sql is much more preferred,

DECLARE @colList AS NVARCHAR(MAX), @sqlStatement AS NVARCHAR(MAX)

SELECT @colList = STUFF((SELECT DISTINCT ',' + QUOTENAME(alerts) 
                    FROM data
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') ,1,1,'')


SET @sqlStatement = 'SELECT plateno,' + @colList + ' 
             FROM 
             (
                SELECT plateno, alerts 
                FROM data
             ) dta
             pivot 
             (
                COUNT(alerts)
                FOR alerts IN (' + @colList + ')
             ) pvt '

EXECUTE(@sqlStatement)

SQLFiddle Demo

OUTPUT

╔═════════╦═══╦═══╦═══╦═══╗
║ PLATENO ║ 1 ║ 2 ║ 3 ║ 4 ║
╠═════════╬═══╬═══╬═══╬═══╣
║ A       ║ 2 ║ 0 ║ 1 ║ 1 ║
║ B       ║ 0 ║ 2 ║ 0 ║ 0 ║
║ C       ║ 0 ║ 1 ║ 0 ║ 0 ║
╚═════════╩═══╩═══╩═══╩═══╝

[see here for other example](http://www.sqlfiddle.com/#!6/75889/1) — John Woo, May 06 '13 at 04:47

score 1 · Answer 3 · answered May 06 '13 at 04:31

Try this:

SELECT plateno
,SUM(CASE alerts WHEN 1 THEN 1 ELSE 0 END) AS [1]
,SUM(CASE alerts WHEN 2 THEN 1 ELSE 0 END) AS [2]
,SUM(CASE alerts WHEN 3 THEN 1 ELSE 0 END) AS [3]
,SUM(CASE alerts WHEN 4 THEN 1 ELSE 0 END) AS [4]
FROM Table1
GROUP BY plateno

Output:

╔═════════╦═══╦═══╦═══╦═══╗
║ PLATENO ║ 1 ║ 2 ║ 3 ║ 4 ║
╠═════════╬═══╬═══╬═══╬═══╣
║ A       ║ 2 ║ 0 ║ 1 ║ 1 ║
║ B       ║ 0 ║ 2 ║ 0 ║ 0 ║
║ C       ║ 0 ║ 1 ║ 0 ║ 0 ║
╚═════════╩═══╩═══╩═══╩═══╝

See this SQLFiddle

score 0 · Answer 4 · answered May 06 '13 at 04:52

Select plateno,sum([1]) as [1],sum([2]) as [2],sum([3]) as [3],sum([4]) as [4]
from 
(
 Select plateno,
  case when alert=1 then 1 else 0 end as [1],
  case when alert=2 then 1 else 0 end as [2],
  case when alert=3 then 1 else 0 end as [3],
  case when alert=4 then 1 else 0 end as [4]
 from planet

 )z
group by plateno

SQL fiddle here

SQL Server query for grouping

4 Answers4

See this SQLFiddle