统计表问题

[jwt1982]

表如下

id hpname sl time
01 1001 20 05-06-01
02 1002 10 05-06-01
03 1001 10 05-06-02
04 1002 15 05-06-02
05 1003 10 05-06-02
--------------------------------------生成结果：
rq 1001 1002.......
05-06-01 20 10
05-06-02 10 15

--------------------------------------

应该如何写SQL语句呢？

[frankwong]

请搜索关键字“交叉表”

[xeqtr1982]

create table tb(id varchar(10),hpname int,sl int,[time] varchar(10))

insert into tb select '01',1001,20,'05-06-01'
union all select '02',1002,10,'05-06-01'
union all select '03',1001,10,'05-06-02'
union all select '04',1002,15,'05-06-02'
union all select '05',1003,10,'05-06-02'

declare @sql varchar(8000)
set @sql='select [time]'
select @sql=@sql+',['+cast(hpname as varchar)+']=sum(case hpname when '''+cast(hpname as varchar)+''' then sl else 0 end)' from tb group by hpname
exec(@sql+' from tb group by [time]')

drop table tb

[sxdoujg]

up

[jwt1982]

呵呵，能不能不用declare @sql varchar(8000)方式，我的列表可能要超过这个数字！

所以才上来问问！

[xeqtr1982]

http://blog.csdn.net/zjcxc/archive/2003/12/29/20075.aspx

邹老大的blog有详细的专题讨论

[yuwenfeng]

给你看一下这个..应该对你有帮助的.....
Table : Temp
Fields List : DepID , DepName, DepCounts
Record List
DepID DepName DepCounts
0001 PMC 200
0002 Noble 100
0003 HR 50
0004 Invoice 60
0005 MHPC 80

Note: 实现将Temp的行与列转换, 写入致另外一表SDepMent ;
例如:
转成:
DepID 0001 0002 0003 0004 0005
DepName PMC Noble HR Invoice MHPC
DepCounts 200 100 50 60 80


Contain :


select * from temp

select * from mytb

select id from sysobjects where [name]='mytb'
select id from sysobjects where [name]='temp'
select * from syscolumns a inner join sysobjects b on a.id=b.id where a.[name]='mytb'

select name from syscolumns where id=(select id from sysobjects where [name]='mytb' )

select name from syscolumns where id=(select id from sysobjects where [name]='temp' ) order by colid

alter procedure trunproc
as
declare @cnt int,@val1 varchar(10),@val2 varchar(10),@val3 varchar(10)
declare @ii int,@sql varchar(100)
select @cnt=count(name) from sysobjects where [name]='mytb'
if (@cnt=0)
begin
create table mytb(
col1 varchar(10) null,
col2 varchar(10) null,
col3 varchar(10) null,
col4 varchar(10) null,
col5 varchar(10) null
)
end
else
begin
delete from mytb
end
insert mytb(col1) select name from syscolumns where id=(select id from sysobjects where [name]='temp' ) order by colid
declare aa cursor for select depId,depname,depcount from temp
open aa
set @ii=2
FETCH NEXT FROM aa INTO @val1,@val2,@val3
WHILE @@FETCH_STATUS = 0
begin
set @sql=' update mytb set col'+ltrim(rtrim(str(@ii)))+'='''+@val1+''' where col1=''depid'''
exec(@sql)
set @sql=' update mytb set col'+ltrim(rtrim(str(@ii)))+'='''+@val2+''' where col1=''depname'''
exec(@sql)
set @sql=' update mytb set col'+ltrim(rtrim(str(@ii)))+'='''+@val3+''' where col1=''depcount'''
exec(@sql)
FETCH NEXT FROM aa INTO @val1,@val2,@val3
set @ii=@ii+1
end
CLOSE aa
DEALLOCATE aa


GO

exec trunproc

[jwt1982]

研究先，解决后回来

[子陌红尘]

exec()执行的语句长度不受限制，因此，可以声明多个varchar(8000)的变量，串行执行：
declare @sql1 varchar(8000),...,@sqln varchar(8000)
set @sql1=...
...
set @sqln=...
exec(@sql1+...+@sqln)

[jwt1982]

改造了一下表结构

如果hpname是在另外一个表中型如：

id hpname

的话我建立交叉表的时候会不会更容易一些？