关于一个Sql问题，求各位大神帮帮忙
表字段存在ID,DeviceType(设备类型),DeptName(部门名称)
，现在根据设备类型和部门名称要做一个统计，展现形式为
如表数据为
ID DeviceType DeptName
1 Computer 部门1
2 Monitor 部门2
3 Computer 部门1
4 Monitor 部门1
现在需要通过sql展现出
DeptName Computer Monitor
部门1 2 1
部门2 1 0
各位大婶怎么看？
[解决办法]
select deptname,sum(case when devicetype='computer' then 1 else 0 end) as computer,sum(case when devicetype='Monitor' then 1 else 0 end) as Monitor
from TB
group by deptname
[解决办法]
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-12-04 10:48:45
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
--Feb 10 2012 19:13:17
--Copyright (c) Microsoft Corporation
--Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[test]
if object_id('[test]') is not null
drop table [test]
go
create table [test]
(
[ID] int,
[DeviceType] varchar(8),
[DeptName] varchar(5)
)
insert [test]
select 1,'Computer','部门1' union all
select 2,'Monitor','部门2' union all
select 3,'Computer','部门1' union all
select 4,'Monitor','部门1'
go
select
[DeptName],
sum(case when [DeviceType]='Computer' then 1 else 0 end) as Computer,
sum(case when [DeviceType]='Monitor' then 1 else 0 end) as Monitor
from
test
group by
[DeptName]
/*
DeptName Computer Monitor
-------- ----------- -----------
部门1 2 1
部门2 0 1
(2 行受影响)
*/
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-12-04 10:48:45
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
--Feb 10 2012 19:13:17
--Copyright (c) Microsoft Corporation
--Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[test]
if object_id('[test]') is not null
drop table [test]
go
create table [test]
(
[ID] int,
[DeviceType] varchar(8),
[DeptName] varchar(5)
)
insert [test]
select 1,'Computer','部门1' union all
select 2,'Monitor','部门2' union all
select 3,'Computer','部门1' union all
select 4,'Monitor','部门1'
go
declare @str varchar(max)
set @str=''
select
@str=@str+','+[DeviceType]+'=sum(case when [DeviceType]='+QUOTENAME([DeviceType],'''')
+' then 1 else 0 end)'
from
test
group by
[DeviceType]
--print @str
exec('select [DeptName]'+@str+' from test group by [DeptName]')
/*
DeptName Computer Monitor
-------- ----------- -----------
部门1 2 1
部门2 0 1
(2 行受影响)
*/
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-12-04 10:48:45
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
--Feb 10 2012 19:13:17
--Copyright (c) Microsoft Corporation
--Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[test]
if object_id('[test]') is not null
drop table [test]
go
create table [test]
(
[ID] int,
[DeviceType] varchar(8),
[DeptName] varchar(5)
)
insert [test]
select 1,'Computer','部门1' union all
select 2,'Monitor','部门2' union all
select 3,'Computer','部门1' union all
select 4,'Monitor','部门1'
go
declare @str varchar(max)
set @str=''
select
@str=@str+','+[DeviceType]+'=sum(case when [DeviceType]='+QUOTENAME([DeviceType],'''')
+' then 1 else 0 end)'
from
test
group by
[DeviceType]
--print @str
set @str='select isnull([DeptName],'+'''合计'''+') as [DeptName]'
+@str+' from test group by [DeptName] with rollup'
exec(@str)
/*
DeptName Computer Monitor
-------- ----------- -----------
部门1 2 1
部门2 0 1
合计 2 2
(3 行受影响)
*/
