一个很难的问题,各位高手请解答(200分)

Question

表A
    ID  Name  Sex    n1   n2   n3   n4  n5  n6 .... n50  sortID
    1   张三  男                                         01
    2   李四  女                                         01
    3   王五  男                                         02表B 
    ID  tzID  tzName     tzValue  sortID
    1   n1    基本工资   500      01
    2   n2    奖金       100      01
    3   n3    加班       50       01
    4   n1    基本工资   200      02
    5   n2    其他       20       02当 sortID=01时的datagrid显示的结果为
   姓名  性别 基本工资 奖金 加班 
   张三  男   500      100  50
   李四  女   500      100  50 
当 sortID=02时的结果
   姓名  性别 基本工资 其他
   王五  男   200      20 
========================================
就是将表B tzID列n1,n2.. 与表A 列 n1,n2对应 
把表B tzName换成表A的列的名字 tzValue 换成表A的记录的值
========================================
同样的问题 这里还有100分 
http://community.csdn.net/Expert/topic/4985/4985638.xml?temp=.9250299

Answer 1

交叉表实现一例 
问题 ：
源表数据：   
ID    NAME    CODE
1       A          AA
2       A          BB
3       B          CC
4       B          DD
5       C          EE
6       C          FF
7       D          GG
8       D          HH
9       E          II
10     E          JJ
希望统计成如下格式：
CODE       A           C           D           E           F           合计    
---------- ----------- ----------- ----------- ----------- ----------- ----- 
AA         1           0           0           0           0           10%
BB         1           0           0           0           0           10%
CC         0           1           0           0           0           10%
DD         0           1           0           0           0           10%
EE         0           0           1           0           0           10%
FF         0           0           1           0           0           10%
GG         0           0           0           1           0           10%
HH         0           0           0           1           0           10%
II         0           0           0           0           1           10%
JJ         0           0           0           0           1           10%实现步骤如下：
--建立测试数据
declare @tmp1 table
(
 id int,
 name varchar(10),
 code varchar(10)
)
insert into @tmp1
select 1,'A','AA'
UNION
select 2,'A','BB'
UNION
select 3,'B','CC'
UNION
select 4,'B','DD'
UNION
select 5,'C','EE'
UNION
select 6,'C','FF'
UNION
select 7,'D','GG'
UNION
select 8,'D','HH'
UNION
select 9,'E','II'
UNION
select 10,'E','JJ'--返回结果
SELECT  CODE,
 sum(A) as 'A',
 sum(B) as 'C',
 sum(C) as 'D',
 sum(D) as 'E',
 sum(E) as 'F',
 left(CAST(COUNT(CODE) AS DECIMAL(10,2))/(SELECT COUNT(*) FROM @TMP1)*100,2) +'%' AS '合计'
FROM 
(SELECT CODE ,
 CASE WHEN NAME='A' THEN count(CODE) ELSE 0 end as A,
 CASE WHEN NAME='B' THEN count(CODE) ELSE 0 end AS B,
 CASE WHEN NAME='C' THEN count(CODE) ELSE 0 end AS C,
 CASE WHEN NAME='D' THEN count(CODE) ELSE 0 end AS D,
 CASE WHEN NAME='E' THEN count(CODE) ELSE 0 end AS E
FROM @tmp1
GROUP BY CODE,name) AS C
GROUP   BY CODE

Answer 2

楼主参考
http://frontalboy.cnblogs.com/archive/2005/06/22/178984.html

Answer 3

Select 姓名=a,name,性别=a.sex,
基本工资=sum(case when b.tzID='n1' then tzValue else 0 end),
奖金 =sum(case when b.tzID='n2' then tzValue else 0 end),
加班 =sum(case when b.tzID='n3' then tzValue else 0 end)
from a inner join b on a.sortID=b.sortID
group by a.name,a.sex

Answer 4

把你表b的数据改一点点
    ID  tzID  tzName     tzValue  sortID
    1   n1    基本工资   500      01
    2   n2    奖金       100      01
    3   n3    加班       50       01
    4   n1    基本工资   200      02
－－－－－－－－－－－－－－－－－－－－－－
    5   n4    其他       20       02
－－－－－－－－－－－－－－－－－－－－－－
Select 姓名=a.name,性别=a.sex,
基本工资=sum(case when b.tzID='n1' then tzValue else 0 end),
奖金 =sum(case when b.tzID='n2' then tzValue else 0 end),
加班 =sum(case when b.tzID='n3' then tzValue else 0 end),
其他 =sum(case when b.tzID='n4' then tzValue else 0 end)
from a inner join b on a.sortID=b.sortID
group by a.name,a.sex

Answer 5

LZ 如果你b表中的tzName值是不定的.可以考虑下用视图+自定义函数写.b表不可能存在这样的两条数据吧(
1   n1    基本工资   500      01
1   n1    基本工资   400      01)
GettableStr=b表值.
GettableStr1=返回的字段Select a.Name,a.Sex,dbo.GettableStr(sortID,dbo.GettableStr1(sortID) as 基本工资字段) as  基本工资,dbo.GettableStr(sortID,dbo.GettableStr2(sortID) as 奖金字段) as  奖金,.... from....

Answer 6

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
B表中的记录不是固定的 是可以任意增加的     ID  tzID  tzName     tzValue  sortID
    1   n1    基本工资   500      01
    2   n2    奖金       100      01
    3   n3    加班       50       01
    4   n1    基本工资   200      02
    5   n2    其他       20       02
    6   n4    加班2      50       01
    7   n5    补贴       320      01
    ...
当 sortID=01时的datagrid显示的结果为
   姓名  性别 基本工资 奖金 加班 加班2   补贴
   张三  男   500      100  50   50      320
   李四  女   500      100  50   50      320
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

Answer 7

但不能超过 n50 这个不用大家考虑 我自己控制
还有 B表中的tzID 的有规则的增加 也是我自己控制的 大家不用考虑

Answer 8

表A
    ID  Name  Sex    n1   n2   n3   n4  n5  n6 .... n50  sortID
    1   张三  男                                         01
    2   李四  女                                         01
    3   王五  男                                         02表B 
    ID  tzID  tzName     tzValue  sortID
    1   n1    基本工资   500      01
    2   n2    奖金       100      01
    3   n3    加班       50       01
    4   n1    基本工资   200      02
    5   n2    其他       20       02CREATE FUNCTION dbo.fnk_GetItem
(@n varchar(20),@sortID varchar(10))  
RETURNS int 
AS  
BEGIN 
declare @value int
select @value = tzValue from B where tzID = @n and sortID=@sortID
return @value
ENDdelete A
delete bcreate table A(id int,name varchar(20),sex char(10),n1 int,n2 int,sortID varchar(10))
insert into A values(1,'张三','男',null,null,'01')
insert into A values(2,'李四','女',null,null,'01')
insert into A values(3,'王五','男',null,null,'02')create table B(id int,tzID varchar(20),tzName char(50),tzValue int,sortID varchar(10))
insert into B values(1,'n1','基本工资',500,'01')
insert into B values(2,'n2','奖金',100,'01')
insert into B values(3,'n3','加班',50,'01')
insert into B values(4,'n1','基本工资',200,'02')
insert into B values(5,'n2','其他',20,'02')--存储过程
create procedure dbo.pd_GetList
(
@sortID varchar(10)
)
as
begin
  select A.id,A.name,A.Sex,dbo.fnk_GetItem('n1',@sortID) as n1,dbo.fnk_GetItem('n2',@sortID) as n2 from A 
where A.sortID = @sortID
end
--测试
exec pd_GetList '02'我简单写了一下，
如果你想nx列实现动态的话，你可以用循环拼字符串

Answer 9

--方法
CREATE FUNCTION dbo.fnk_GetItem
(@n varchar(20),@sortID varchar(10))  
RETURNS int 
AS  
BEGIN 
declare @value int
select @value = tzValue from B where tzID = @n and sortID=@sortID
return @value
END--存储过程
create procedure dbo.pd_GetList
(
@sortID varchar(10)
)
as
begin
  select A.id,A.name,A.Sex,dbo.fnk_GetItem('n1',@sortID) as n1,dbo.fnk_GetItem('n2',@sortID) as n2 from A 
where A.sortID = @sortID
end

Answer 10

To:zjlion(晴海) 
=============== 
先谢回复 我调试了你的方法 
select A.id,A.name,A.Sex,dbo.fnk_GetItem('n1',@sortID) as n1,dbo.fnk_GetItem('n2',@sortID) as n2 from A 是静态的 怎么能根据数据库来实现列的显示呢 请指教

Answer 11

把昨天的存储过程修改如下：
alter procedure dbo.pd_GetList
(
@sortID varchar(10)
)
as
begin
  declare @sql varchar(1024),@itemlist varchar(1024)
set @itemlist = ''

select @itemlist = @itemlist + 
case when exists(select 1 from B where syscolumns.name = b.tzID) 
then 'dbo.fnk_GetItem('''+syscolumns.name + ''','''+@sortID+''') as '+syscolumns.name  
else syscolumns.name end+',' 
from ..syscolumns 
inner join ..sysobjects on ..sysobjects.id = ..syscolumns.id
where  ..sysobjects.xtype='u' and ..sysobjects.name = 'A'

set @itemlist = substring(@itemlist,0,len(@itemlist))

set @sql = 'select ' + @itemlist + ' from A where sortID = ''' +@sortID+''''

print @sql

exec (@sql)
end
--测试
exec pd_GetList '02'

Answer 12

To zjlion(晴海) 
多谢回复 你写的程序 我看不太懂 调试还可以 修改就完了 
等我慢慢看看

Answer 13

我看看SQL里面的Case语句就知道了。

Answer 14

小弟愚钝 谁能帮我改改下面的程序呀
alter procedure dbo.pd_GetList
(
@sortID varchar(10)
)
as
begin
  declare @sql varchar(1024),@itemlist varchar(1024)
set @itemlist = ''

select @itemlist = @itemlist + 
case when exists(select 1 from B where syscolumns.name = b.tzID) 
then 'dbo.fnk_GetItem('''+syscolumns.name + ''','''+@sortID+''') as '+syscolumns.name  
else syscolumns.name end+',' 
from ..syscolumns 
inner join ..sysobjects on ..sysobjects.id = ..syscolumns.id
where  ..sysobjects.xtype='u' and ..sysobjects.name = 'A'

set @itemlist = substring(@itemlist,0,len(@itemlist))

set @sql = 'select ' + @itemlist + ' from A where sortID = ''' +@sortID+''''

print @sql

exec (@sql)
end

Answer 15

你现在就是要把表A(A)和表B(B)替换为你数据库中实际的表名就可以了，还要怎么改？

Answer 16

syscolumns 是什么？
 ..sysobjects.xtype='u' and ..sysobjects.name = 'A' 怎么还有2个..

Answer 17

对了，还有那个函数'dbo.fnk_GetItem里用到表A和表B的地方，也要对应改过来。

Answer 18

我数据库表 是根据你的
create table A(id int,name varchar(20),sex char(10),n1 int,n2 int,sortID varchar(10))
insert into A values(1,'张三','男',null,null,'01')
.......
建的

Answer 19

有msn没有？算了，我得[email protected]

Answer 20

exec pd_GetList '02'
结果如下
id  n1  n2  name sex sortID
3   200 20  王五 男  02
列名 
n1 n2 应该对应的为 基本工资 其他

Answer 21

把列名换一下 ：
添加一个函数做转换
create function dbo.fnk_GetItemName
(
@tzID   varchar(10),
@sortID varchar(10)
) RETURNS varchar(50)
as 
begin
declare @tzName varchar(50) select @tzName = tzName from B where tzID = @tzID and sortID = @sortID
return @tzName
end 
存储过程修改如下：
alter procedure dbo.pd_GetList
(
@sortID varchar(10)
)
as
begin
  declare @sql varchar(1024),@itemlist varchar(1024)
set @itemlist = ''

select @itemlist = @itemlist + 
case when exists(select 1 from B where syscolumns.name = b.tzID) 
then 'dbo.fnk_GetItem('''+syscolumns.name + ''','''+@sortID+''') as '+ dbo.fnk_GetItemName(syscolumns.name,@sortID)
else syscolumns.name end+',' 
from ..syscolumns 
inner join ..sysobjects on ..sysobjects.id = ..syscolumns.id
where  ..sysobjects.xtype='u' and ..sysobjects.name = 'A'

set @itemlist = substring(@itemlist,0,len(@itemlist))

set @sql = 'select ' + @itemlist + ' from A where sortID = ''' +@sortID+''''

print @sql

exec (@sql)
end

Answer 22

Select 姓名=a.name,性别=a.sex,
基本工资=sum(case when b.tzID='n1' then tzValue else 0 end),
奖金 =sum(case when b.tzID='n2' then tzValue else 0 end),
加班 =sum(case when b.tzID='n3' then tzValue else 0 end),
其他 =sum(case when b.tzID='n4' then tzValue else 0 end)
from a inner join b on a.sortID=b.sortID
group by a.name,a.sex

Answer 23

========================
To zjlion(晴海)
========================
我弄了弄 代码如下 用的是你的库 A，B
declare @sql varchar(8000)
set @sql = 'select a.name as 姓名,a.sex as 性别,'
select @sql = @sql + 'case tzName when '''+tzName +''' 
                          then tzvalue end as '''+tzName+''','
  from (select distinct tzName  from b ) as a
select @sql = left(@sql,len(@sql)-1) + ' from B,(select name ,sex  from A) as A where sortID=02'
exec (@sql)
但结果是重复的 如下
=========================================
姓名  性别  基本工资  加班  奖金  其他
张三  男    200       0     0     0
李四  女    200       0     0     0
王五  男    200       0     0     0
张三  男    0         0     0     20
李四  女    0         0     0     20
王五  男    0         0     0     20
========================================
我想要的是姓名  性别  基本工资  加班  奖金  其他
张三  男    200       0     0     20
李四  女    200       0     0     20
王五  男    200       0     0     20
=======================================请帮我把上面的代码改改 多谢了！！

Answer 24

xinfan(新凡) 
你的我试了试,实在不好改.
我得这个已经完全满足你的要求了(前面的我就不写了,就是把存储过程又改了些.)
alter procedure dbo.pd_GetList
(
@sortID varchar(10)
)
as
begin
 declare @sql varchar(1024),@itemlist varchar(1024)
set @itemlist = 'a.name as 姓名,a.sex as 性别, 'select @itemlist = @itemlist + 
case when exists(select 1 from B where syscolumns.name = b.tzID) 
then 'dbo.fnk_GetItem('''+syscolumns.name + ''','''+@sortID+''') as '+ dbo.fnk_GetItemName(syscolumns.name,@sortID) +',' else '' end 
from ..syscolumns 
inner join ..sysobjects on ..sysobjects.id = ..syscolumns.id
where  ..sysobjects.xtype='u' and ..sysobjects.name = 'A'set @itemlist = substring(@itemlist,0,len(@itemlist))set @sql = 'select ' + @itemlist + ' from A where sortID = ''' +@sortID+''''print @sqlexec (@sql)
end