22,207
社区成员
发帖
与我相关
我的任务
分享
--这样?
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-08 17:15:32
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([StartDate] datetime,[MoCode] varchar(20),[MDeptCode] int,[iQty] int)
insert [tb]
select '2009-12-01','MO20090001',2001,1000 union all
select '2009-12-01','MO20090002',2002,500 union all
select '2009-12-01','MO20090003',2002,500 union all
select '2009-12-02','MO20090004',2001,1000 union all
select '2009-12-02','MO20090005',2002,500 union all
select '2009-12-02','MO20090006',2002,500 union all
select '2009-12-02','MO20090007',2003,1000
--------------开始查询--------------------------
select
month(StartDate) as [month],
convert(varchar(10),StartDate,120) as Date,
max(case MDeptCode when 2001 then MoCode+'('+ltrim(MDeptCode)+')' else '' end) as a,
max(case MDeptCode when 2002 then MoCode+'('+ltrim(MDeptCode)+')' else '' end) as b,
max(case MDeptCode when 2003 then MoCode+'('+ltrim(MDeptCode)+')' else '' end) as c
from
tb
group by
month(StartDate),convert(varchar(10),StartDate,120)
----------------结果----------------------------
/* month Date a b c
----------- ---------- ---------------------------------- ---------------------------------- ----------------------------------
12 2009-12-01 MO20090001(2001) MO20090003(2002)
12 2009-12-02 MO20090004(2001) MO20090006(2002) MO20090007(2003)
(2 行受影响)
*/