----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-08-09 14:40:54
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([品号] int,[日期] varchar(5),[数量] int)
insert [A]
select 1001,'06-08',11 union all
select 1001,'06-09',12 union all
select 1001,'06-10',13 union all
select 1001,'06-11',14 union all
select 1001,'06-12',15
--------------开始查询--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([日期])+'=max(case when [日期]='+quotename([日期],'''')+' then [数量] else 0 end)'
from [A] group by [日期]
exec('select [品号]'+@s+' from A group by [品号]')
----------------结果----------------------------
/*
品号 06-08 06-09 06-10 06-11 06-12
----------- ----------- ----------- ----------- ----------- -----------
1001 11 12 13 14 15
*/
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-08-09 14:40:54
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([品号] int,[日期] varchar(5),[数量] int)
insert [A]
select 1001,'06-08',11 union all
select 1001,'06-09',12 union all
select 1001,'06-10',13 union all
select 1001,'06-11',14 union all
select 1001,'06-12',15
--------------开始查询--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([日期])+'=max(case when [日期]='+quotename([日期],'''')+' then [数量] else 0 end)'
from [A] group by [日期]
exec('select [品号]'+@s+' from A group by [品号]')
----------------结果----------------------------
/*
品号 06-08 06-09 06-10 06-11 06-12
----------- ----------- ----------- ----------- ----------- -----------
1001 11 12 13 14 15
*/