sql - SQL Server:交叉表/数据透视表

我花了2天的时间试图使它生效，但没有任何解决方案。

我将数据汇总到一个PriceList View 中

| Category     | Product     | QtyFrom | QtyTo   | Price   |
------------------------------------------------------------
| Category#1   | Product#1   | 1       | 9       | 4.99    |
| Category#1   | Product#2   | 1       | 9       | 5.99    |
| Category#1   | Product#3   | 1       | 9       | 6.99    |
| Category#1   | Product#1   | 10      | 49      | 4.75    |
| Category#1   | Product#2   | 10      | 49      | 5.75    |
| Category#1   | Product#3   | 10      | 49      | 6.75    |
| Category#1   | Product#1   | 50      | 99      | 4.50    |
| Category#1   | Product#2   | 50      | 99      | 5.50    |
| Category#1   | Product#3   | 50      | 99      | 6.50    |
| Category#1   | Product#1   | 100     | 999     | 4.25    |
| Category#1   | Product#2   | 100     | 999     | 5.25    |
| Category#1   | Product#3   | 100     | 999     | 6.25    |
| Category#2   | Product#4   | 1       | 9       | 4.99    |
| Category#2   | Product#5   | 1       | 10      | 5.99    |
| Category#2   | Product#6   | 1       | 9       | 6.99    |
| Category#2   | Product#4   | 10      | 49      | 4.75    |
| Category#2   | Product#5   | 11      | 50      | 5.75    |
| Category#2   | Product#6   | 10      | 49      | 6.75    |
| Category#2   | Product#4   | 50      | 99      | 4.50    |
| Category#2   | Product#5   | 51      | 99      | 5.50    |
| Category#2   | Product#6   | 50      | 99      | 6.50    |

我需要对此进行交叉制表以生成价格表。最终结果将按需要返回结果的单个类别进行查询

| Category#1   |  1  |  9  |  10  |  49  |  50  |  99  |
--------------------------------------------------------
| Product#1    |    4.99   |     4.75    |     4.50    |
| Product#2    |    5.99   |     5.75    |     5.50    |
| Product#3    |    6.99   |     6.75    |     6.50    |

或喜欢

| Category#2   |  1  |  9  |  1  |  10  |  10  |  49  |
--------------------------------------------------------
| Product#4    |    4.99   |             |     4.75    |
| Product#5    |           |     5.99    |             |
| Product#6    |    6.99   |             |     6.75    |

如您所见，取决于谁输入数据，他们可能会犯错并导致组内的价格跌落不一致。但是我仍然需要返回3个最低的QtyFrom / QtyTo行(一旦有了这些数据，我将生成一个单独的报告，仅显示每个类别的价格突破数量，以便他们可以在生成价格清单和调整价格突破之前运行该行)

某些类别可能没有3个价格下降点，其他类别可能有3个以上的价格下跌点。但是我总是需要3列。

使用SQL Server 2014

先感谢您。

最佳答案

您是否尝试过使用conditional Aggregate

SELECT Product,
       Max(CASE WHEN QtyFrom >= 1 AND QtyTo <= 9 THEN Price END) [1 | 9],
       Max(CASE WHEN QtyFrom >= 10 AND QtyTo <= 49 THEN Price END) [10 | 49],
       Max(CASE WHEN QtyFrom >= 50 AND QtyTo <= 99 THEN Price END) [50 | 99]
FROM   Yourtable
WHERE  Category = 'Category#1'
GROUP  BY Product

或使用Pivot。在数据透视表源查询中，您需要创建范围列以在数据透视表列列表中使用它。

SELECT *
FROM  (SELECT CONVERT(VARCHAR(50), QtyFrom) + ' | '
              + CONVERT(VARCHAR(50), QtyTo) AS Rang,
              Product,
              price
       FROM   Yourtable
       WHERE  Category = 'Category#1') a
      PIVOT (Max(price)
            FOR Rang IN ([1 | 9],
                         [10 | 49],
                         [50 | 99]))piv

更新:如果您不知道价格下跌，请使用Dynamic Pivot

DECLARE @cols VARCHAR(max)='',
        @sql  NVARCHAR(max)

SET @cols = (SELECT DISTINCT '[' + CONVERT(VARCHAR(50), QtyFrom) + ' | '
                             + CONVERT(VARCHAR(50), QtyTo) + '],'
             FROM   Yourtable
             WHERE  Category = 'Category#1'
             FOR xml path(''))

SELECT @cols = LEFT(@cols, Len(@cols) - 1)

SET @sql = 'SELECT *
    FROM  (SELECT CONVERT(VARCHAR(50), QtyFrom) + '' | ''
                  + CONVERT(VARCHAR(50), QtyTo) AS Rang,
                  Product,
                  price
           FROM   Yourtable
           WHERE  Category = ''Category#1'') a
          PIVOT (Max(price)
                FOR Rang IN (' + @cols + '))piv '

EXEC Sp_executesql  @sql

关于sql - SQL Server:交叉表/数据透视表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/28524954/