August 24, 2012 at 5:19 am
Hi,
I have one table name crime with thre column name id, Category and Date
ex:Crime table
id Category Date
-------------------------------------------------------------
1 Murder 2012-08-11 18:45:55.780
2 Murder 2010-07-11 17:45:55.780
3 Robery 2012-08-11 18:45:55.780
4 Robery 2010-07-11 17:45:55.780
I want to display
Category 2010 2011 2012
-------------------------------------------------
Murder 1 0 or null 1
Robery 1 0 or null 1
What is the query for this Please help me
August 24, 2012 at 5:37 am
This is a simple way of doing it:
create table #crime
(
id int,
Category varchar(10),
[Date] DateTime
);
insert #crime values ( 1, 'Murder', '2012-08-11 18:45:55.780' );
insert #crime values ( 2, 'Murder', '2010-07-11 17:45:55.780' );
insert #crime values ( 3, 'Robbery', '2012-08-11 18:45:55.780' );
insert #crime values ( 4, 'Robbery', '2010-07-11 17:45:55.780' );
/*
I want to display
Category 2010 2011 2012
-------------------------------------------------
Murder 1 0 or null 1
Robery 1 0 or null 1
*/
select Category,
YR2010 = SUM(case when [Date] >= '01 Jan 2010' AND [Date] < '01 Jan 2011' THEN 1 ELSE 0 END),
YR2011 = SUM(case when [Date] >= '01 Jan 2011' AND [Date] < '01 Jan 2012' THEN 1 ELSE 0 END),
YR2012 = SUM(case when [Date] >= '01 Jan 2012' AND [Date] < '01 Jan 2013' THEN 1 ELSE 0 END)
from #crime
group by Category
August 24, 2012 at 6:06 am
You can do this using the PIVOT table feature of SQL Server. Read more here.
For your problem:
IF OBJECT_ID ('tempdb..#CrimeDetails') IS NOT NULL
BEGIN
DROP TABLE #CrimeDetails
END
GO
CREATE TABLE #CrimeDetails
(
ID INT,
Category VARCHAR(100),
CrimeDate DATETIME
)
GO
INSERT INTO #CrimeDetails VALUES ('1', 'Murder', '2012-08-10 18:45:55.780')
INSERT INTO #CrimeDetails VALUES ('1', 'Murder', '2012-08-10 18:45:55.780')
INSERT INTO #CrimeDetails VALUES ('1', 'Murder', '2012-08-10 18:45:55.780')
INSERT INTO #CrimeDetails VALUES ('2', 'Murder', '2010-07-11 17:45:55.780')
INSERT INTO #CrimeDetails VALUES ('3', 'Robery', '2011-08-10 18:45:55.780')
INSERT INTO #CrimeDetails VALUES ('3', 'Robery', '2011-08-10 18:45:55.780')
INSERT INTO #CrimeDetails VALUES ('4', 'Robery', '2010-07-11 17:45:55.780')
GO
--SELECT ID,Category,DATEPART(YEAR,CrimeDate) AS CrimeYear FROM #CrimeDetails
GO
SELECT Category,[2010],[2011],[2012]
FROM(
SELECT ID,Category, DATEPART(YEAR,CrimeDate) AS CrimeYear FROM #CrimeDetails
) TheSourceTable
PIVOT(
COUNT (ID) FOR CrimeYear IN ([2010],[2011],[2012])
) ThePivotTable;
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply