create table A
(
服装名称 varchar(10),
颜色名称 varchar(10),
尺码名称 varchar(5),
数量 int
)
insert A select 'A服装','黑','L',20
insert A select 'A服装','黑','XL',10
insert A select 'A服装','黑','XXL',12
insert A select 'A服装','红','L',50
insert A select 'A服装','红','XL',40
insert A select 'B服装','红','6',50
insert A select 'B服装','红','8',40
declare @T_SQL varchar(8000)
set @T_SQL=''
select @T_SQL=@T_SQL + 'sum(case when 尺码名称=''' + 尺码名称 + ''' then 数量 else 0 end) as ''' + 尺码名称 + ''','
from (select Distinct 尺码名称 from A) T
set @T_SQL='select 服装名称,颜色名称,' + left(@T_SQL,len(@T_SQL)-1) + ' from A group by 服装名称,颜色名称'
exec (@T_SQL)