交叉报表

最新推荐文章于 2016-09-12 17:52:15 发布

hgxl

最新推荐文章于 2016-09-12 17:52:15 发布

阅读量611

点赞数

分类专栏： database 文章标签：报表 sql null table go object

本文链接：https://blog.csdn.net/hgxl/article/details/265499

版权

database 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tabone]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tabone]
GO

CREATE TABLE [dbo].[tabone] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[m] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[n] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[a] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE proc tabone_p
as
declare @sql varchar(8000)
set @sql='select case when(GROUPING(m)=1) then '+'''all'''+' else isnull(m,'+'''合计 '''+') end as m'
select @sql=@sql + ',sum(case n when '''+rtrim(n)+''' then a else '''''+'end)['+rtrim(n) + ']'
from (select distinct n from tab) a
select @sql=@sql+' from tabone group by m,m with rollup'
exec(@sql)
print(@sql)
GO