sql中数列变横列的问题

/*测试数据

;WITH table1(门店编号,商品号,员工销售号,属性,销售数量,单价,销售额) AS (

    SELECT 33301,44401,02,101,10,10,100 UNION ALL

    SELECT 33301,44402,03,101,20,15,300 UNION ALL

    SELECT 33301,44402,03,102,10,15,150 UNION ALL

    SELECT 33302,44401,04,101,10,10,100 UNION ALL

    SELECT 33303,44401,02,102,10,10,100 UNION ALL

    SELECT 33303,44402,03,102,20,15,300

)

SELECT * INTO 表一 FROM table1



;WITH table2(属性,属性名称) AS (

    SELECT 101,'洗化' UNION ALL

    SELECT 102,'食品' 

)

SELECT * INTO 表二 FROM table2

*/



DECLARE @sql varchar(max)

DECLARE @columns varchar(max)

DECLARE @aliases varchar(max)

DECLARE @total varchar(max)



SET @columns = ''

SET @aliases = ''

SET @total = ''



SELECT @columns = @columns+',['+Convert(varchar(11),属性)+']',

       @aliases = @aliases+',ISNULL(['+Convert(varchar(11),属性)+'],0) AS ['+属性名称+'销售额]',

       @total = @total+'+ISNULL(['+Convert(varchar(11),属性)+'],0)'

  FROM 表二



SET @columns = STUFF(@columns,1,1,'')

SET @aliases = STUFF(@aliases,1,1,'')

SET @total = STUFF(@total,1,1,'')



SET @sql = '

SELECT 门店编号,销售员,

       '+@total+' 销售总额,

       '+@aliases+'

  FROM (SELECT 门店编号,员工销售号 销售员,属性,销售额

          FROM 表一

       ) t

 PIVOT (

        SUM(销售额)

        FOR 属性 IN ('+@columns+')

       ) p'



PRINT @sql



EXEC(@sql)

SELECT 门店编号,销售员,

       ISNULL([101],0)+ISNULL([102],0) 销售总额,

       ISNULL([101],0) AS [洗化销售额],ISNULL([102],0) AS [食品销售额]

  FROM (SELECT 门店编号,员工销售号 销售员,属性,销售额

          FROM 表一

       ) t

 PIVOT (

        SUM(销售额)

        FOR 属性 IN ([101],[102])

       ) p

   门店编号      销售员    销售总额  洗化销售额  食品销售额

----------- ----------- ----------- ----------- -----------

      33301           2         100         100           0

      33303           2         100           0         100

      33301           3         450         300         150

      33303           3         300           0         300

      33302           4         100         100           0

CREATE TABLE #TB1
(
	[门店编号]  INT,
	[商品号]    INT,
	[员工销售号] CHAR(5),
	[属性]       INT,
	[销售数量]   INT,
	[单价]        INT,
	[销售额] INT
)
INSERT INTO #TB1
SELECT 33301,44401,'02',101,10,10,100 UNION ALL
SELECT 33301,44402,'03',101,20,15,300 UNION ALL
SELECT 33301,44402,'03',102,10,15,150 UNION ALL
SELECT 33302,44401,'04',101,10,10,100 UNION ALL
SELECT 33303,44401,'02',102,10,10,100 UNION ALL
SELECT 33303,44402,'03',102,20,15,30

SELECT 
	[门店编号],
	[员工销售号],
	ISNULL([101],0)+ISNULL([102],0) AS [销售总额],
	ISNULL([101],0) AS [洗化销售额],
	ISNULL([102],0) AS [食品销售额] 
FROM 
(SELECT [门店编号],[员工销售号],[属性],[销售额] FROM #TB1) a
pivot (sum([销售额]) for [属性]  in ([101],[102])) P

门店编号        员工销售号 销售总额        洗化销售额       食品销售额
----------- ----- ----------- ----------- -----------
33301       02    100         100         0
33303       02    100         0           100
33301       03    450         300         150
33303       03    30          0           30
33302       04    100         100         0

(5 行受影响)