员工表：
员工号，员工姓名
部门表：
部门号，部门名称
员工、部门关联表：
部门号，员工号
如果我想新建一个视图，显示如下信息：
部门名称 员工1 员工2 员工3
那么该视图的select语句应该如何写啊？
select
------解决方案--------------------
create table #emp
(empid varchar(10),
empname varchar(20)
)
create table #dept
(deptid varchar(10),
deptname varchar(20)
)
create table #EDRelation
(empid varchar(10),
deptid varchar(10)
)
insert into #emp values('001','aa')
insert into #emp values('002','bb')
insert into #emp values('003','cc')
insert into #emp values('004','dd')
insert into #emp values('005','EE')
insert into #emp values('006','FF')
insert into #dept values('IT1','it')
insert into #dept values('AC1','ac')
insert into #dept values('CW1','CW')
insert into #EDRelation values('001','IT1')
insert into #EDRelation values('002','IT1')
insert into #EDRelation values('003','AC1')
insert into #EDRelation values('004','CW1')
insert into #EDRelation values('005','CW1')
insert into #EDRelation values('006','IT1')
IF OBJECT_ID('TEMPDB..#DUAL') IS NOT NULL
DROP TABLE #DUAL
select * ,'员工'+编号 as 员工编号
into #dual
from (
SELECT convert(varchar(10),ROW_NUMBER() OVER(PARTITION BY #DEPT.DEPTID ORDER BY #EMP.EMPID ))AS 编号,
#emp.*,#dept.*from #emp,#dept, #EDRelation
where #emp.empid=#EDRelation.empid
and #dept.deptid=#EDRelation.deptid)a
declare @sql varchar(8000)
set @sql = 'select deptname '
select @sql = @sql + ' , max(case 编号 when ''' + 编号 + ''' then empname else '''' end) '''+员工编号+''''
from (select distinct 编号,员工编号 from #dual) as a
set @sql = @sql + ' from #dual group by deptname'
exec(@sql)
------解决方案--------------------
create table employee (empno int identity primary key,ename varchar(20))
create table dept (deptno int identity primary key ,dname varchar(20))
create table dept_employee (id int identity primary key ,deptno int ,empno int)
insert into dept(dname)
select '研发部' union
select '销售部' union
select '工程部'
insert into employee( ename)
select '张三' union
select '李四' union
select '王五' union
select '八斤' union