pivot行转列问题

[lzylin]

有关行转列的问题，现有表 P(privid int ,moduid varchar(50),operid varchar(50)),记录如下所示
privid moduid operid
1 01 view
2 0101 add
3 0101 select
4 02 view
5 0201 add
6 0201 select
7 0201 delete

想通过查询语句得出以下结果
moduid [add] [select] [delete]
01 add select null
02 add select null
或
moduid [add] [select] [delete]
01 1 1 0
02 1 1 1

[lzylin]

表达的意思有出入，我写的意思是记录应该是
想通过查询语句得出以下结果
moduid [view] [add] [select] [delete]
01 view null null null
0101 null add select null
02 null add select delete
或
moduid [view] [add] [select] [delete]
01 1 0 0 0
0101 0 1 1 0
02 1 0 0 0
0201 0 1 1 1

不好意思呀

[dawugui]

create table tb(privid int,moduid varchar(10),operid varchar(10))
insert into tb values(1 ,'01'   ,'view')
insert into tb values(2 ,'0101' ,'add')
insert into tb values(3 ,'0101' ,'select')
insert into tb values(4 ,'02'   ,'view')
insert into tb values(5 ,'0201' ,'add')
insert into tb values(6 ,'0201' ,'select')  
insert into tb values(7 ,'0201' ,'delete')
go

select * from (select left(moduid,2) moduid , operid from tb) a pivot (max(operid) for operid in ([add],[select],[delete])) b

drop table tb

/*
moduid add        select     delete
------ ---------- ---------- ----------
01     add        select     NULL
02     add        select     delete

(2 行受影响)
*/

[--小F--]

[Quote=引用 1 楼 dawugui 的回复:]
SQL code
create table tb(privid int,moduid varchar(10),operid varchar(10))
insert into tb values(1 ,'01' ,'view')
insert into tb values(2 ,'0101' ,'add')
insert into tb values(3 ,'0101' ,'select……
[/Quote]
速度太快了

[kevn]

select moduid,
[add] = select count(*) from p where p.moduid = t.moduid and operid = 'add'
[select] = select count(*) from p where p.moduid = t.moduid and operid = 'select',
[delete] = select count(*) from p where p.moduid = t.moduid and operid = 'delete'
from p t group by moduid

[dawugui]

[Quote=引用 2 楼 fredrickhu 的回复:]
速度太快了[/Quote]
过了六分钟了，还快？

[kevn]

[Quote=引用 3 楼 kevinwen0413 的回复:]
SQL code
select moduid,
[add] = select count(*) from p where p.moduid = t.moduid and operid = 'add'
[select] = select count(*) from p where p.moduid = t.moduid and operid = 'select',
[delete] = se……
[/Quote]
看错了。。。

[华夏小卒]

--> 测试数据: [bb]
if object_id('[bb]') is not null drop table [bb]
go 
create table [bb] (privid int,moduid varchar(4),operid varchar(6))
insert into [bb]
select 1,'01','view' union all
select 2,'0101','add' union all
select 3,'0101','select' union all
select 4,'02','view' union all
select 5,'0201','add' union all
select 6,'0201','select' union all
select 7,'0201','delete'

select 
	moduid=left(moduid,2),
	[add]=sum([add]),
	[select]=sum([select]),
	[delete]=sum([delete])
from [bb]
pivot
(
	count(operid)
	for operid in ([add],[select],[delete])
)t
group by left(moduid,2)
 
moduid add         select      delete
------ ----------- ----------- -----------
01     1           1           0
02     1           1           1

(2 行受影响)