求SQL语句,急~~
表结构:
name type category
------------------------
N1 T1 C1
N2 T1 C2
N3 T2 C1
N4 T3 C3
N5 T2 C4
N6 T3 C5
N1 T1 C1
统计结果:
Category name T1 T2 T3
----------------------------
C1 N1 2 0 0
C1 N3 0 1 0
C2 N2 1 0 0
C3 N4 0 0 1
C4 N5 0 1 0
C5 N6 0 0 1
请大家帮忙看看
[解决办法]
select Category, name,
sum(case when type= 'T1 ' then 1 else 0 end ) as T1,
sum(case when type= 'T2 ' then 1 else 0 end ) as T2,
sum(case when type= 'T3 ' then 1 else 0 end ) as T3
from tblname
group by Category , name
[解决办法]
樓上.
[解决办法]
如果T1 T2 T3 T4...不固定就不好弄了
[解决办法]
drop table #test
create table #test(name nvarchar(20),type nvarchar(20),category nvarchar(20))
select * from #test
insert into #test(name,type,category) values ( 'n1 ', 't1 ', 'c1 ');
insert into #test(name,type,category) values ( 'n2 ', 't1 ', 'c2 ');
insert into #test(name,type,category) values ( 'n3 ', 't2 ', 'c1 ');
insert into #test(name,type,category) values ( 'n4 ', 't3 ', 'c3 ');
insert into #test(name,type,category) values ( 'n5 ', 't2 ', 'c4 ');
insert into #test(name,type,category) values ( 'n6 ', 't3 ', 'c5 ');
insert into #test(name,type,category) values ( 'n1 ', 't1 ', 'c1 ');
select category,name,sum(case when type= 't1 ' then 1 else 0 end),sum(case when type= 't2 ' then 1 else 0 end),sum(case when type= 't3 ' then 1 else 0 end) from #test group by name,category
[解决办法]
mark
------解决方案--------------------
路过,sql文盲
[解决办法]
create table #
(
name nvarchar(20),
type nvarchar(20),
category nvarchar(20)
)
insert into #
select 'n1 ', 't1 ', 'c1 ' union all
select 'n2 ', 't1 ', 'c2 ' union all
select 'n3 ', 't2 ', 'c1 ' union all
select 'n4 ', 't3 ', 'c3 ' union all
select 'n5 ', 't2 ', 'c4 ' union all
select 'n6 ', 't3 ', 'c5 ' union all
select 'n1 ', 't1 ', 'c1 '
declare @sql varchar(8000)
set @sql = 'select category, name '
select@sql = @sql + ', sum(case type when ' ' ' + type + ' ' ' then 1 else 0 end) as ' ' ' + type + ' ' ' '
from (select distinct type from #) x
set @sql = @sql + ' from # group by category, name order by category, name '
select category, name, type from # order by category, name, type
exec (@sql)
drop table #
[解决办法]
--如果type固定只有三種
Select
category,
name,
SUM(Case type When 'T1 ' Then 1 Else 0 End) As T1,
SUM(Case type When 'T2 ' Then 1 Else 0 End) As T2,
SUM(Case type When 'T3 ' Then 1 Else 0 End) As T3
From
TEST
Group By
category,
name
Order By
category,
name
--如果type不固定
--使用動態SQL語句
Declare @S Varchar(8000)
Select @S = 'Select category, name '
Select @S = @S + ', SUM(Case type When ' ' ' + type + ' ' ' Then 1 Else 0 End) As ' + type
From TEST Group By type
Select @S = @S + ' From TEST Group By category, name Order By category, name '
EXEC(@S)
[解决办法]
winer2006(我心飞扬)
select Category, name,
sum(case when type= 'T1 ' then 1 else 0 end ) as T1,
sum(case when type= 'T2 ' then 1 else 0 end ) as T2,
sum(case when type= 'T3 ' then 1 else 0 end ) as T3
from tblname
group by Category , name
----------------------------------
应该可以的
[解决办法]
paoluo(一天到晚游泳的鱼) ( )
---------
又见高手!来.net版
[解决办法]
顶!
[解决办法]
厉害 收藏
[解决办法]
MARK，以后整理
[解决办法]
paoluo(一天到晚游泳的鱼) 的不错.
--如果type不固定
--使用動態SQL語句
Declare @S Varchar(8000)
Select @S = 'Select category, name '
Select @S = @S + ', SUM(Case type When ' ' ' + type + ' ' ' Then 1 Else 0 End) As ' + type
From TEST Group By type
Select @S = @S + ' From TEST Group By category, name Order By category, name '
EXEC(@S)
GO
[解决办法]
学习
[解决办法]
lZ等下结，我来接下分
[解决办法]
学习ing..
------解决方案--------------------
xxing
[解决办法]
up
