下面一组数据:
LH PSHM ZL
4F60 001 0.231
4F60 002 0.321
4PP 001 0.921
4PP 002 0.212
4PP 002 0.092
4PP 002 0.123
4AX 001 0.211
4AX 002 0.128
...
期望结果一:(即根据‘LH’名称进行,可能产生N个 LH PSHM ZL 抬头)
LH PSHM ZL LH PSHM ZL LH PSHM ZL
4F60 001 0.231 4PP 001 0.921 4AX 001 0.211
4F60 002 0.321 4PP 002 0.212 4AX 002 0.128
4PP 002 0.092
4PP 002 0.123
4F60 合计 0.552 4PP 合计 1.348 4AX 合计 0.339(*注意:可能浏览器显示问题,LH '4PP'为一列对齐)
========================================================================
期望结果2:(即根据‘PSHM’名称进行,可能产生N个 PSHM)
LH 001 002 ... 合计
4F60 0.231 0.321 ... 0.552
4PP 0.921 0.427 ... 1.348
4AX 0.211 0.128 ... 0.339
....
合计 1.363 0.876 ... 2.239 *注:其中LH 为4PP的 002 列自动进行了合计
LH PSHM ZL
4F60 001 0.231
4F60 002 0.321
4PP 001 0.921
4PP 002 0.212
4PP 002 0.092
4PP 002 0.123
4AX 001 0.211
4AX 002 0.128
...
期望结果一:(即根据‘LH’名称进行,可能产生N个 LH PSHM ZL 抬头)
LH PSHM ZL LH PSHM ZL LH PSHM ZL
4F60 001 0.231 4PP 001 0.921 4AX 001 0.211
4F60 002 0.321 4PP 002 0.212 4AX 002 0.128
4PP 002 0.092
4PP 002 0.123
4F60 合计 0.552 4PP 合计 1.348 4AX 合计 0.339(*注意:可能浏览器显示问题,LH '4PP'为一列对齐)
========================================================================
期望结果2:(即根据‘PSHM’名称进行,可能产生N个 PSHM)
LH 001 002 ... 合计
4F60 0.231 0.321 ... 0.552
4PP 0.921 0.427 ... 1.348
4AX 0.211 0.128 ... 0.339
....
合计 1.363 0.876 ... 2.239 *注:其中LH 为4PP的 002 列自动进行了合计
第二种可以这样select
isnull(lh,'合计') as lh,
max(case when '001' then ZL else 0.0 end) as '001',
........
sum(PSHM) as 合计
from
tb
group by
lh
with rollup动态的不写了 精华帖子N多 关键是 group by with rollup
用法在这里 自己看看吧 不懂再帮你写
第二个, 使用 code=SQL]select name from tempdb.sys.syscolumns where id = object_id('tempdb..#TA')[[/code]读取列信息,构造动态的piovt语句,
右侧合计那一列也同样方法构造,
下面合计那一列,使用with cet as()得出上面的结果后
用 select * from cet
union all
select '合计',sum XXX from cet 这样的形式
==========
合计 1.363 0.876 ... 2.239
-- Author :fredrickhu(小F,向高手学习)
-- Date :2012-09-17 09:13:14
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([LH] varchar(10),[PSHM] varchar(3),[ZL] numeric(4,3))
insert [tb]
select '4F60','001',0.231 union all
select '4F60','002',0.321 union all
select '4PP','001',0.921 union all
select '4PP','002',0.212 union all
select '4PP','002',0.092 union all
select '4PP','002',0.123 union all
select '4AX','001',0.211 union all
select '4AX','002',0.128
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select isnull(lh,''合计'') as lh'
select @sql = @sql + ' , sum(case PSHM when ''' + PSHM + ''' then ZL else 0 end) [' + PSHM + ']'
from (select distinct PSHM from tb) as a
set @sql = @sql + ',sum(ZL) as 合计 from tb group by isnull(lh,''合计'') with rollup'
exec(@sql)
----------------结果----------------------------
/* lh 001 002 合计
---------- --------------------------------------- --------------------------------------- ---------------------------------------
4AX 0.211 0.128 0.339
4F60 0.231 0.321 0.552
4PP 0.921 0.427 1.348
NULL 1.363 0.876 2.239(4 行受影响)*/
insert [tb]
select '4F60','001',0.231 union all
select '4F60','002',0.321 union all
select '4PP','001',0.921 union all
select '4PP','002',0.212 union all
select '4PP','002',0.092 union all
select '4PP','002',0.123 union all
select '4AX','001',0.211 union all
select '4AX','002',0.128declare @sql varchar(8000)
select @sql =isnull(@sql+'],[','')+type from
(select top 100 * from
(select 'LH'+'-'+cast(dense_rank() over (order by LH) as nvarchar(10)) as type from tb
union
select 'PSHM'+'-'+cast(dense_rank() over (order by LH) as nvarchar(10)) from tb
union
select 'ZL'+'-'+cast(dense_rank() over (order by LH) as nvarchar(10)) from tb
) a
order by right(type,1),type) a
set @sql = '[' + @sql + ']'
exec ('select * from (
select cast(row_number() over (partition by LH order by PSHM) as nvarchar(10)) as 类型,LH as value ,''LH''+''-''+cast(dense_rank() over (order by LH) as nvarchar(10)) as type from tb
union all
select cast(row_number() over (partition by LH order by PSHM) as nvarchar(10)) as 类型,PSHM,''PSHM''+''-''+cast(dense_rank() over (order by LH) as nvarchar(10)) from tb
union all
select cast(row_number() over (partition by LH order by PSHM) as nvarchar(10)) as 类型,cast(ZL as nvarchar(10)),''ZL''+''-''+cast(dense_rank() over (order by LH) as nvarchar(10)) from tb
union all
select ''999999999'',LH,''LH''+''-''+cast(dense_rank() over (order by LH) as nvarchar(10)) as type from tb
group by LH
union all
select ''999999999'',''合计'',''PSHM''+''-''+cast(dense_rank() over (order by LH) as nvarchar(10)) as type from tb
group by LH
union all
select ''999999999'',cast(SUM(ZL) as nvarchar(10)),''ZL''+''-''+cast(dense_rank() over (order by LH) as nvarchar(10)) as type from tb
group by LH) a
pivot (max(value) for type in ('+@sql+')) b')
类型 LH-1 PSHM-1 ZL-1 LH-2 PSHM-2 ZL-2 LH-3 PSHM-3 ZL-3
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 4AX 001 0.211 4F60 001 0.231 4PP 001 0.921
2 4AX 002 0.128 4F60 002 0.321 4PP 002 0.212
3 NULL NULL NULL NULL NULL NULL 4PP 002 0.092
4 NULL NULL NULL NULL NULL NULL 4PP 002 0.123
999999999 4AX 合计 0.339 4F60 合计 0.552 4PP 合计 1.348(5 行受影响)