求一条SQL 2005 动态行转列的语句

[jack4238]

[dawugui]

无图无真相.

[cs_lb]

调整图片再提问 呵呵

[billpu]

改图片仅限百度用户交流使用

[jack4238]

呵呵，因为我是用到了二个表，所以新建了一个临时表来存放数据，然后再动态行转列，问题已经解决，谢谢！！！！

[jack4238]

不好意思，忘记百度对图片有限制了，更新了图片

[AcHerat]

--生成测试数据
create	table	a_detail
(
	unitname	nvarchar(10),
	productca	nvarchar(10),
	proname		nvarchar(10),
	quantity	int,
	saleprice	int
)

insert	a_detail	select	'技术部','固定资产','笔记本','9','10000'
insert	a_detail	select	'技术部','固定资产','路由器','2','200'
insert	a_detail	select	'研发部','固定资产','笔记本','15','9000'
insert	a_detail	select	'研发部','固定资产','路由器','3','200'
insert	a_detail	select	'研发部','固定资产','投影仪','1','2000'
insert	a_detail	select	'测试部','固定资产','笔记本','4','9000'
insert	a_detail	select	'测试部','耗材',	'晒鼓'	,'6','150'

declare	@sql	varchar(8000)
set	@sql	= 'select * from ('
set	@sql	= @sql + 'select proname as product'
select	@sql	= @sql + ',sum(case unitname when '''+unitname+''' then quantity else 0 end) ['+unitname+']'
from	(select distinct unitname from a_detail)U			--把所有唯一的单位名称都列举出来
select	@sql	= @sql + ' from a_detail group by proname,productca'
set	@sql	= @sql + ' union all '
set	@sql	= @sql + 'select productca as product'
select	@sql	= @sql + ',max(case unitname when '''+unitname+''' then saleprice else 0 end ) ['+unitname+']'
from	(select distinct unitname from a_detail)N
select	@sql	= @sql + ' from (select productca,unitname,sum(quantity*saleprice) as saleprice from a_detail group by unitname,productca) as T group by productca) as M'
exec	(@sql)

drop	table	a_detail

product    测试部         技术部         研发部
---------- ----------- ----------- -----------
笔记本        4           9           15
路由器        0           2           3
投影仪        0           0           1
晒鼓          6           0           0
固定资产      36000       90400       137600
耗材          900         0           0

(6 行受影响)

[AcHerat]

排版太累！！！ 和SQL里的完全不一样！

[回头是岸]

[让你望见影子的墙]

5楼很强大

[百年树人]

declare @sql1 varchar(8000),@sql2 varchar(8000)
select
  @sql1=isnull(@sql1+',','')
  +'sum(case when unitname='''+unitname+''' then quantity else 0 end) as ['+unitname+']',
  @sql2=isnull(@sql2+',','')
  +'sum(case when unitname='''+unitname+''' then saleprice*quantity else 0 end) as ['+unitname+']'
from
(select distinct unitname from a_detail) t

set @sql1='select proname,'+@sql1+' from a_detail group by proname'
set @sql2='select productca+''合计'','+@sql2+' from a_detail group by productca'

exec (@sql1+' union all '+@sql2)

[AcHerat]

[Quote=引用 9 楼 josy 的回复:]
SQL code
declare @sql1 varchar(8000),@sql2 varchar(8000)
select
@sql1=isnull(@sql1+',','')
+'sum(case when unitname='''+unitname+''' then quantity else 0 end) as ['+unitname+']',
@sql2=isnu……
[/Quote]

学习！！！ 刚学到动态这部分！ 大家不要笑....