SQL行列转置，不使用pivot
商品号 订单数量
S1 80
S1 180
S1 280
S1 380
S1 480
S2 80
S2 180
S2 380
S2 280
S2 280
S3 80
S1 80
S1 180
S1 580
转置成
商品号 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
------解决方案--------------------
----------------------------------------------------------------
-- 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
*/
------解决方案--------------------
你把exec改成print就出来语句了