sql中数列变横列的问题

[szmggn]

有两个表：
表一：
门店编号 商品号 员工销售号 属性 销售数量 单价 销售额
33301 44401 02 101 10 10 100
33301 44402 03 101 20 15 300
33301 44402 03 102 10 15 150
33302 44401 04 101 10 10 100
33303 44401 02 102 10 10 100
33303 44402 03 102 20 15 300
在一个门店一个销售元销售的某一个商品的数量和金额

表二：

属性 属性名称
101 洗化
102 食品

现在想做一个表：

门店编号 销售员 销售总额 洗化销售额 食品销售额
33301 02 10 10 0
33301 03 30 10 20
33302 04 10 10 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 行受影响)

• 打赏
• 举报

回复 赞

[szmggn]

首先感谢上面那位大哥的回答，这种方法我会，目前的情况是有一千多个商品，属性有20多种，如果这样子写的话，会把人累死的

• 打赏
• 举报

回复 赞

[Tiger_Zhao]

看你的表二就知道你需要动态语句

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

PRINT 输出

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

• 打赏
• 举报

回复 赞