city style color size qty
长沙 S6MF01002 152 46 1
长沙 S6MF01002 152 48 2
长沙 S6MF01002 152 50 2
长沙 S6MF01002 152 52 1
长沙 S6MF01002 201 46 1
长沙 S6MF01002 201 48 2
长沙 S6MF01002 201 50 2
长沙 S6MF01002 201 52 1
上面到下面的样子
city style color 46 48 50 52
长沙 S6MF01002 152 1 2 2 1
长沙 S6MF01002 201 1 2 2 1
长沙 S6MF01002 152 46 1
长沙 S6MF01002 152 48 2
长沙 S6MF01002 152 50 2
长沙 S6MF01002 152 52 1
长沙 S6MF01002 201 46 1
长沙 S6MF01002 201 48 2
长沙 S6MF01002 201 50 2
长沙 S6MF01002 201 52 1
上面到下面的样子
city style color 46 48 50 52
长沙 S6MF01002 152 1 2 2 1
长沙 S6MF01002 201 1 2 2 1
Insert Test Select '长沙','S6MF01002',152,46,1
Union all Select '长沙','S6MF01002',152,48,2
Union all Select '长沙','S6MF01002',152,50,2
Union all Select '长沙','S6MF01002',152,52,1
Union all Select '长沙','S6MF01002',201,46,1
Union all Select '长沙','S6MF01002',201,48,2
Union all Select '长沙','S6MF01002',201,50,2
Union all Select '长沙','S6MF01002',201,52,1
DECLARE @S VARCHAR(1000)
SET @S='SELECT City,Styple,Color '
SELECT @S=@S+',['+Rtrim(Size)+']=SUM(CASE WHEN Size='''+Rtrim(Size)+''' THEN qty ELSE 0 END)'
FROM (Select Distinct Size From TEST) A Order By Size
SET @S=@S+' FROM TEST GROUP BY City,Styple,Color'
EXEC(@S)