以下为示例的表结构：
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语句，自己研究一下，很有用的。