多表查询列转行-SQL语句

[chai1338]

表userType（用户类型）

表menu（菜单：一级菜单、二级菜单）

表quanxian（用户类型对应的菜单）

数据库设计的不是很理想，想请问下 如何用SQL建个临时表组合出如下的效果

用户类型 信息编辑 合同执行 查询功能（所有不重复2级菜单）
管理员 1 1 1
置业顾问 0 1 0
财务 0 0 0
（所有类型）

尝试了下只能得到

select userType.userTypeName,menu.parentNode,userType.id from userType
left join quanxian on quanxian.userTypeName=userType.userTypeName
left join menu on menu.id=quanxian.menuId
group by userType.userTypeName,menu.parentNode,userType.id
order by userType.id
//userType加了个Id排序用

[發糞塗牆]

你的数据直接贴出来吧。别截图了，不想一个一个敲

[chai1338]

引用 1 楼 DBA_Huangzj 的回复:

你的数据直接贴出来吧。别截图了，不想一个一个敲

userType表
id          userTypeName
----------- --------------------------------------------------
1           管理员
2           置业顾问
3           财务
4           公司领导
5           普通员工
menu表
id   parentNode   childNode    url           orderID
---------- --------------------------------------------------
1    信息编辑   查找房源  bumen.aspx            1
2    信息编辑   客户登记  kehuDengji.aspx       2
3    信息编辑   客户管理  kehuManage.aspx       3
4    信息编辑   房源管理  FYManage.aspx         4
5    合同执行   收款登记  shoukuanDJ.aspx       5
6    合同执行   合同变更  hetongUpdate.aspx     6
7    合同执行   欠款催收  qiankuanCS.aspx       7
8    查询功能   房源查询  foundFY.aspx          8
9    查询功能   客户查询  KHCX.aspx             9
10   查询功能   合同查询  HTCX.aspx             10
quanxian表
userTypeName   menuId      UInsert UDelect UUpdate USelect
------------- ----------- ------- ------- ------- -------
管理员          1           1       1       1       1
管理员          2           1       1       1       1
管理员          3           1       1       1       1
管理员          4           1       1       1       1
管理员          5           1       1       1       1
管理员          6           1       1       1       1
管理员          7           1       1       1       1
管理员          8           1       1       1       1
管理员          9           1       1       1       1
管理员          10          1       1       1       1
置业顾问        1           1       1       1       1
置业顾问        2           1       1       1       1
置业顾问        5           1       1       1       1
置业顾问        7           1       1       1       1

想要得到最后的结果 用户类型 信息编辑 合同执行 查询功能 管理员 1 1 1 置业顾问 0 1 0 财务 0 0 0 公司领导 0 0 0 普通员工 0 0 0

[chai1338]

多谢版主大人 ，那么晚还在解答问题

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-01-09 23:49:33
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) 
--	Jun 17 2011 00:57:23 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[userType]
if object_id('[userType]') is not null drop table [userType]
go 
create table [userType]([id] int,[userTypeName] varchar(8))
insert [userType]
select 1,'管理员' union all
select 2,'置业顾问' union all
select 3,'财务' union all
select 4,'公司领导' union all
select 5,'普通员工'

--> 测试数据：[menu]
if object_id('[menu]') is not null drop table [menu]
go 
create table [menu]([id] int,[parentNode] varchar(8),[childNode] varchar(8),[url] varchar(17),[orderID] int)
insert [menu]
select 1,'信息编辑','查找房源','bumen.aspx',1 union all
select 2,'信息编辑','客户登记','kehuDengji.aspx',2 union all
select 3,'信息编辑','客户管理','kehuManage.aspx',3 union all
select 4,'信息编辑','房源管理','FYManage.aspx',4 union all
select 5,'合同执行','收款登记','shoukuanDJ.aspx',5 union all
select 6,'合同执行','合同变更','hetongUpdate.aspx',6 union all
select 7,'合同执行','欠款催收','qiankuanCS.aspx',7 union all
select 8,'查询功能','房源查询','foundFY.aspx',8 union all
select 9,'查询功能','客户查询','KHCX.aspx',9 union all
select 10,'查询功能','合同查询','HTCX.aspx',10

--> 测试数据：[quanxian]
if object_id('[quanxian]') is not null drop table [quanxian]
go 
create table [quanxian]([userTypeName] varchar(8),[menuId] int,[UInsert] int,[UDelect] int,[UUpdate] int,[USelect] int)
insert [quanxian]
select '管理员',1,1,1,1,1 union all
select '管理员',2,1,1,1,1 union all
select '管理员',3,1,1,1,1 union all
select '管理员',4,1,1,1,1 union all
select '管理员',5,1,1,1,1 union all
select '管理员',6,1,1,1,1 union all
select '管理员',7,1,1,1,1 union all
select '管理员',8,1,1,1,1 union all
select '管理员',9,1,1,1,1 union all
select '管理员',10,1,1,1,1 union all
select '置业顾问',1,1,1,1,1 union all
select '置业顾问',2,1,1,1,1 union all
select '置业顾问',5,1,1,1,1 union all
select '置业顾问',7,1,1,1,1
--------------开始查询--------------------------


--------------开始查询--------------------------

--select * from [userType]
--select * from [menu]
SELECT usertypename,[查询功能]=max(CASE WHEN parentnode='查询功能' THEN 1 ELSE 0 END ),
[合同执行]=max(CASE WHEN parentnode='合同执行' THEN 1 ELSE 0 END ),
[信息编辑]=max(CASE WHEN parentnode='信息编辑' THEN 1 ELSE 0 END )
FROM (
SELECT DISTINCT
        c.usertypename ,
        --a.usertypename ,
        b.parentnode
FROM    usertype c
        LEFT JOIN [quanxian] a
        INNER JOIN menu b ON a.menuid = b.id ON c.usertypename = a.usertypename
        )a
GROUP BY usertypename
----------------结果----------------------------
/* 
usertypename 查询功能        合同执行        信息编辑
------------ ----------- ----------- -----------
财务           0           0           0
公司领导         0           0           0
管理员          1           1           1
普通员工         0           0           0
置业顾问         0           1           1

(5 行受影响)
*/