----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-17 16:21:37
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([商品号] varchar(2),[订单数量] int)
insert [huang]
select 'S1',80 union all
select 'S1',180 union all
select 'S1',280 union all
select 'S1',380 union all
select 'S1',480 union all
select 'S2',80 union all
select 'S2',180 union all
select 'S2',380 union ALL
select 'S2',280 union all
select 'S2',280 union all
select 'S3',80 union all
select 'S1',80 union all
select 'S1',180 union all
select 'S1',580
--------------开始查询--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([订单数量])+'=count(case when [订单数量]='+quotename([订单数量],'''')+' then 1 else null end)'
from [huang] group by [订单数量]
exec('select [商品号]'+@s+' from [huang] group by [商品号]')
----------------结果----------------------------
/*
商品号 80 180 280 380 480 580
---- ----------- ----------- ----------- ----------- ----------- -----------
S1 2 2 1 1 1 1
S2 1 1 2 1 0 0
S3 1 0 0 0 0 0
*/ |
