有一表数据如下
id ddate num
0010400785 2014-01-12 1
0010400785 2014-01-13 2
0010400785 2014-01-14 1
0010400785 2014-01-18 1
0010400785N 2014-01-03 1
0010400785N 2014-01-11 2
0010400785N 2014-01-13 1
0010400785N 2014-01-17 1
0010400785N 2014-01-20 2
0010400785N 2014-01-25 1
比如要查询2014-01-01到2014-01-31（日期段可随便选）如何将数据根据对应的日期显示如下
2014-01-01 2014-01-02 2014-01-03 ....2014-01-12 2014-01-13 合计
0010400785 0 0 1 2 3
0010400785N 0 0 1 0 1 .....2
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-25 16:18:09
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] nvarchar(22),[ddate] datetime,[num] int)
insert [huang]
select '0010400785','2014-01-12',1 union all
select '0010400785','2014-01-13',2 union all
select '0010400785','2014-01-14',1 union all
select '0010400785','2014-01-18',1 union all
select '0010400785N','2014-01-03',1 union all
select '0010400785N','2014-01-11',2 union all
select '0010400785N','2014-01-13',1 union all
select '0010400785N','2014-01-17',1 union all
select '0010400785N','2014-01-20',2 union all
select '0010400785N','2014-01-25',1
--------------生成数据--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([ddate])+'=max(case when [ddate]='+quotename([ddate],'''')+' then [num] else 0 end)'
from huang group by [ddate]
exec('select [id]'+@s+' from huang group by [id]')
----------------结果----------------------------
/*
id Jan 3 2014 12:00AM Jan 11 2014 12:00AM Jan 12 2014 12:00AM Jan 13 2014 12:00AM Jan 14 2014 12:00AM Jan 17 2014 12:00AM Jan 18 2014 12:00AM Jan 20 2014 12:00AM Jan 25 2014 12:00AM
---------------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- -------------------
0010400785 0 0 1 2 1 0 1 0 0