经典的产生报表SQL语句

以下为示例的表结构：
CREATE TABLE [dbo].[drmEstimatedDorm](
 [EstimatedDormId] [int] IDENTITY(1,1) NOT NULL,
 [BUCode] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
 [BU] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
 [Gender] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
 [IsEngineerClass] [bit] NOT NULL CONSTRAINT [DF_drmEstimatedDorm_IsEngineerClass]  DEFAULT ((0)),
 [ApplyYear] [int] NOT NULL CONSTRAINT [DF_drmEstimatedDorm_Year]  DEFAULT ((0)),
 [ApplyWeek] [int] NOT NULL CONSTRAINT [DF_drmEstimatedDorm_Week]  DEFAULT ((0)),
 [WeekBeginDate] [datetime] NOT NULL,
 [WeekEndDate] [datetime] NOT NULL,
 [EstimatedCount] [int] NOT NULL CONSTRAINT [DF_drmEstimatedDorm_EstimatedCount]  DEFAULT ((0)),
 [ActualCount] [int] NOT NULL CONSTRAINT [DF_drmEstimatedDorm_ActiveCount]  DEFAULT ((0)),
 [DifferrenceRate] [decimal](18, 4) NOT NULL,
 CONSTRAINT [PK_drmEstimatedDorm] PRIMARY KEY CLUSTERED
(
 [EstimatedDormId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
以下为示例SQL语句：
DECLARE @sql varchar(8000)
SET @sql = 'SELECT * FROM ('
SELECT @sql = @sql + char(10)+char(9)+'SELECT CASE WHEN (GROUPING(BUCode)=1) THEN ''ALL'' ELSE ISNULL(BUCode,''UNKNOWN'') END AS BUCode,'
SELECT @sql=@sql+char(10)+char(9)+'CASE WHEN (GROUPING(Gender)=1) THEN ''ALL'' ELSE ISNULL(Gender,''UNKNOWN'') END AS Gender'
SELECT @sql = @sql + ','+char(10)+char(9)+'SUM(CASE WHEN ApplyWeek='+convert(varchar(10),ApplyWeek)+' AND IsEngineerClass='+convert(varchar(10),IsEngineerClass)+' THEN EstimatedCount end) AS ['+convert(varchar(10),ApplyWeek)+'_'+convert(varchar(10),IsEngineerClass)+']'
FROM (SELECT distinct ApplyWeek,IsEngineerClass FROM dbo.drmEstimatedDorm) AS a
ORDER BY ApplyWeek,IsEngineerClass DESC
SELECT @sql = @sql + ','+char(10)+char(9)+'SUM(CASE WHEN IsEngineerClass='+convert(varchar(10),IsEngineerClass)+' THEN EstimatedCount end) AS [hj_'+convert(varchar(10),IsEngineerClass)+']'
FROM (SELECT distinct IsEngineerClass FROM dbo.drmEstimatedDorm) AS b
ORDER BY IsEngineerClass DESC
SELECT @sql = @sql+char(10)+char(9)+'FROM drmEstimatedDorm '
SELECT @sql = @sql+char(10)+char(9)+'GROUP BY BUCode,Gender WITH CUBE'+char(10)+') AS c '
SELECT @sql = @sql+char(10)+'WHERE Gender <> ''All'' '+char(10)+'ORDER BY BUCode DESC,Gender'
PRINT @sql
EXEC(@sql)
大家可以看打印到屏幕的SQL语句，自己研究一下，很有用的。

转载于:https://www.cnblogs.com/brawei/archive/2007/08/26/870562.html