CREATE TABLE [dbo].[Schedule](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SchedueDate] [varchar](50) NULL,
    [Area] [varchar](50) NULL,
    [TotalAM] [int] NULL CONSTRAINT [DF_Schedule_TotalAM]  DEFAULT ((0)),
    [FactAM] [int] NULL CONSTRAINT [DF_Schedule_FactAM]  DEFAULT ((0)),
    [TotalPM] [int] NULL CONSTRAINT [DF_Schedule_TotalPM]  DEFAULT ((0)),
    [FactPM] [int] NULL CONSTRAINT [DF_Schedule_FactPM]  DEFAULT ((0)),
 CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

ID     SchedueDate   Area   TotalAM   FactAM   TotalPM   FactPM
1      2012-06-07    香港      12       12        12       12
2      2012-06-07    九龍      12       12        12       12
3      2012-06-08    香港      12       12        12       12
4      2012-06-08    九龍      12       12        12       12

select Area,'FactAM' as col,
    sum(case when scheduedate='2012-06-07' then TotalAm else 0 end) as [2012-06-07],
    sum(case when scheduedate='2012-06-08' then TotalAm else 0 end) as [2012-06-08]
from [Schedule] group by Area
union all
select Area,'FactPM',
    sum(case when scheduedate='2012-06-07' then FactAM else 0 end),
    sum(case when scheduedate='2012-06-08' then FactAM else 0 end)
from [Schedule] group by Area
union all
select 
    Area,'Total',sum(FactAM),sum(FactPM)
from [Schedule] group by Area order by 1 desc,2 desc
/*
Area                           col    2012-06-07  2012-06-08
------------------------------ ------ ----------- -----------
香港                             Total  24          24
香港                             FactPM 12          12
香港                             FactAM 12          12
九龍                             Total  24          24
九龍                             FactPM 12          12
九龍                             FactAM 12          12
*/

------解决方案--------------------

给你写了第一个，后面两个一样的方法实现
declare @sql varchar(200)
select @sql=ISNULL(@sql+',','')+'sum(case when SchedueDate='''+LTRIM(SchedueDate)+''' then TotalPM end) as ['+LTRIM(SchedueDate)+']'
from [Schedule] group by SchedueDate
exec('select '+@sql+' from [Schedule] ')

------解决方案--------------------
探讨


引用:

SQL code

select Area,'FactAM' as col,
sum(case when scheduedate='2012-06-07' then TotalAm else 0 end) as [2012-06-07],
sum(case when scheduedate='2012-06-08' then TotalAm else 0 ……


------解决方案--------------------

探讨


引用:

引用:

引用:

SQL code

select Area,'FactAM' as col,
sum(case when scheduedate='2012-06-07' then TotalAm else 0 end) as [2012-06-07],
sum(case when scheduedate='……