SQL SERVER 如何竖列变横列

[season8862008]

例如：
款号 尺码 数量
1 110 2
1 120 4
1 130 10

显示结果是

款号 110 120 130
1 2 4 10

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-06-26 15:26:22
-- Version:
--      Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) 
--	Jun 28 2012 08:36:30 
--	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]([款号] int,[尺码] int,[数量] int)
insert [huang]
select 1,110,2 union all
select 1,120,4 union all
select 1,130,10
--------------生成数据--------------------------

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([尺码])+N'=max(case when [尺码]=N'+quotename([尺码],'''')+N' then [数量] else 0 end)'
from [huang] group by [尺码]
exec(N'select [款号]'+@s+N' from [huang] group by [款号]')
----------------结果----------------------------
/* 
款号          110         120         130
----------- ----------- ----------- -----------
1           2           4           10
*/

[發糞塗牆]

这是静态的，纯练手，看你的数据应该是动态的

SELECT *
FROM huang a PIVOT(MAX([数量]) FOR [尺码] IN ([110],[120],[130]))AS b

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(发粪涂墙)
-- Date    :2014-06-26 15:26:22
-- Version:
--      Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) 
--	Jun 28 2012 08:36:30 
--	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]([款号] int,[尺码] int,[数量] int)
insert [huang]
select 1,110,2 union all
select 1,120,4 union all
select 1,130,10
--------------生成数据--------------------------
--2000写法
declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([尺码])+N'=max(case when [尺码]=N'+quotename([尺码],'''')+N' then [数量] else 0 end)'
from [huang] group by [尺码]
exec(N'select [款号]'+@s+N' from [huang] group by [款号]')
go
--2000以上写法
declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([尺码]) from [huang] group by[尺码]
exec('select * from [huang] pivot (max([数量]) for [尺码] in('+@s+'))b')
----------------结果----------------------------
/* 
款号          110         120         130
----------- ----------- ----------- -----------
1           2           4           10

(1 row(s) affected)

款号          110         120         130
----------- ----------- ----------- -----------
1           2           4           10

*/