联合查询按字段排序的交叉表问题
有两个表
Employee表格式如下:
id number name
1 001 张三
2 002 李四
3 003 王五
...
WageList表格式如下:
id wid serial caption value
3 1 1 CC 30
3 1 2 BB 20
3 1 3 AA 10
1 1 1 CC 40
1 1 2 BB 30
1 1 3 AA 40
2 1 1 CC 20
2 1 2 BB 50
2 1 3 AA 10
3 2 1 CC 30
...
想得到如下交叉表个格式caption位置按serial字段排序：
id number name CC BB AA ...
1 001 张三 40 40 30
2 002 李四 20 50 10
3 003 王五 30 20 10
查询条件wid=1，这个交叉查询怎么写？
[解决办法]
create table Employee(id int,number varchar(10),name varchar(20))
insert Employee select 1, '001 ', '张三 '
union all select 2, '002 ', '李四 '
union all select 3, '003 ', '王五 '
create table WageList(id int,wid int,serial int,caption varchar(10),value int)
insert WageList select 3,1,1, 'CC ',30
union all select 3,1,2, 'BB ',20
union all select 3,1,2, 'AA ',10
union all select 1,1,1, 'CC ',40
union all select 1,1,2, 'BB ',30
union all select 1,1,3, 'AA ',40
union all select 2,1,1, 'CC ',20
union all select 2,1,2, 'BB ',50
union all select 2,1,3, 'AA ',10
union all select 3,2,1, 'CC ',30
declare @sql varchar(8000)
set @sql = ' '
select @sql = @sql + ' ,sum(case caption when ' ' ' + caption + ' ' ' then value else 0 end) [ ' + caption + '] '
from WageList group by caption
set @sql = 'select Employee.id,number,name '+@sql + ' from Employee inner join WageList
on Employee.id=WageList.id
where wid=1
group by Employee.id,number,name '
exec(@sql)
drop table WageList,Employee
[解决办法]
--楼主给的结果有点问题吧
/*
应该是
id number name CC BB AA
1 001 张三 40 30 40
2 002 李四 20 50 10
3 003 王五 30 20 10
*/
create table #Employee(id int, number varchar(100), name varchar(100))
insert into #Employee
select 1, '001 ', '张三 ' union all
select 2, '002 ', '李四 ' union all
select 3, '003 ', '王五 '
create table #WageList(id int,wid int,serial int,caption varchar(100),value int)
insert into #WageList
select 3, 1, 1, 'CC ', 30 union all
select 3, 1, 2, 'BB ', 20 union all
select 3, 1, 3, 'AA ', 10 union all
select 1, 1, 1, 'CC ', 40 union all
select 1, 1, 2, 'BB ', 30 union all
select 1, 1, 3, 'AA ', 40 union all
select 2, 1, 1, 'CC ', 20 union all
select 2, 1, 2, 'BB ', 50 union all
select 2, 1, 3, 'AA ', 10 union all
select 3, 2, 1, 'CC ', 30
declare @s varchar(8000)
set @s = ' '
select @s = @s + ' ,sum(case caption when ' ' ' + caption + ' ' ' then value else null end) as [ ' + caption + '] '
from #WageList
group by caption
order by min(serial)
print @s
set @s = 'select a.id,a.number,a.name ' + @s + '
from #Employee a inner join #WageList b on a.id=b.id
where b.wid=1
group by a.id,a.number,a.name
order by a.id '
exec (@s)
drop table #Employee,#WageList
[解决办法]
Create Table Employee(id Int, number Varchar(10), name Varchar(20))
Insert Employee Select 1, '001 ', '张三 '
Union All Select 2, '002 ', '李四 '
Union All Select 3, '003 ', '王五 '
Create Table WageList(id Int, wid Int, serial Int, caption Varchar(10), value Int)
Insert WageList Select 3,1,1, 'CC ',30
Union All Select 3,1,2, 'BB ',20
Union All Select 3,1,3, 'AA ',10
Union All Select 1,1,1, 'CC ',40
Union All Select 1,1,2, 'BB ',30
Union All Select 1,1,3, 'AA ',40
Union All Select 2,1,1, 'CC ',20
Union All Select 2,1,2, 'BB ',50
Union All Select 2,1,3, 'AA ',10
Union All Select 3,2,1, 'CC ',30
GO
Declare @S Varchar(8000)
Set @S = 'Select A.id, number, name '
Select @S = @S + ' , SUM(Case caption When ' ' ' + caption + ' ' ' Then value Else 0 End) [ ' + caption + '] '
From WageList Group By caption Order By Min(serial) --加上排序
Set @S = @S + ' From Employee A Inner Join WageList B On A.id = B.id Where wid = 1 Group By A.id, number, name '
EXEC(@S)
GO
Drop Table WageList, Employee
--Result
/*
idnumbernameCCBBAA
1001张三403040
2002李四205010
3003王五302010
*/
[解决办法]
HappyMajia(伊壁鸠鲁) ( ) 信誉：100 Blog 加为好友 2007-7-6 20:14:12 得分: 0
还有一个问题，value字段原来为为float类型，如果改为varchar则会出现：
"将 varchar 值 '.9 ' 转换为数据类型为 int 的列时发生语法错误。 "
其实value字段放的都是数字，如果有小数点就会出现上述错误提示
------------
這麼修改下
Declare @S Varchar(8000)
Set @S = 'Select A.id, number, name '
Select @S = @S + ' , MAX(Case caption When ' ' ' + caption + ' ' ' Then value Else ' ' End) [ ' + caption + '] '
From WageList where wid = 1 Group By caption Order By Min(serial) --加上排序
Set @S = @S + ' From Employee A Inner Join WageList B On A.id = B.id Where wid = 1 Group By A.id, number, name '
EXEC(@S)
GO
