22,302
社区成员




/*测试数据
;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 行受影响)