SQL Server选择查询动态列输出

Question

我想在 SQL Server 2008 中完成以下操作

我有一个如下文章表

| ArticleId | Description |
|-----------+-------------|
|         1 | Test        |
|-----------+-------------|

还有这样的订单预测表。

| ArticleId | Week | Order | Amount |
|-----------+--------------+--------+
|         1 |   51 |     1 |      0 |
|         1 |   52 |     2 |    150 |
|         1 |    1 |     3 |      0 |
|         1 |    2 |     4 |    200 |
|         1 |    3 |     5 |      0 |
|-----------+------+-------+--------+

有没有办法创建一个查询，按照< code>order列的顺序为预测表中的每条记录生成一列。如果可能的话，我怎么能这样做呢？

| ArticleId | Description | Week51 | Week52 | Week1 | Week2 | Week3 |
|-----------+-------------+-----------------+-------+-------+-------+
|         1 | Test        |     0  |    150 |     0 |   200 |     0 |
|-----------+-------------+--------+--------+-------+-------+-------+

Answer 1

您可以使用Pivot尝试下面的查询来获得您想要的结果。

Create Table Article (ArticleId Int, [Description] Varchar(10))
Insert Into Article Values (1, 'Test')

Create Table OrderForecast(ArticleId Int, [Week] Int, [Order] Int, Amount Int)
Insert Into OrderForecast Values (1, 51, 1, 0),(1, 52, 2, 150), (1, 1, 3, 0),(1, 2, 4, 200), (1, 3, 5,0)

Select ArticleId, [Description], Week51, Week52, Week1, Week2, Week3
from
(
  select ArticleId, [Description], Amount, [Week]
  from 
    (
    SELECT OrderForecast.ArticleId, 'Week' + Convert(Varchar(10), OrderForecast.[Week]) as [Week], [Order], Amount, 
        Article.[Description] as [Description] FROM OrderForecast
    Inner Join Article On OrderForecast.ArticleId = Article.ArticleId
    )a
) d
pivot
(
  max(Amount)
  for [Week] in (Week51, Week52, Week1, Week2, Week3)
) piv;

结果将如下所示

ArticleId   Description Week51  Week52  Week1   Week2   Week3
-------------------------------------------------------------
1           Test        0       150     0       200     0

在这里，我使用查询作为表格，因为周是 1、2 等数字，但您希望结果在 Week1、Week2 等中。所以我在数字中连接了单词周，并在透视查询中使用它。

你可以在这里找到现场演示现场演示

Answer 2

在一般情况下，SQL语言严格要求在查询编译时知道结果列的数量和数据类型。在准备执行计划并开始查找数据之前，您在这里要求的内容无法知道。

因此，您通常能够做的最好的事情是将其作为三个单独的步骤运行：

1. 运行查询以告诉您要使用的列。
2. 使用#1中的结果即时编写新查询。
3. 执行新查询并返回结果

即便如此，这种透视通常在您的客户端代码或报告工具中处理得更好。唯一的好消息是，仍然可以用一个长SQL字符串从大多数平台上完成所有这些。

对于这种特定的情况，在您清楚地查看周数的情况下，您可以通过预先假设所有53个可能的周（而不是52个，因为年底有部分周！），并编写一个大的SQL语句，手动计算所有55列（53周文章和描述）。

Answer 3

如果WEEK编号和订单编号一致，保持列顺序是一件小事。

您可能会注意到我使用了#预测和#文章，因为我不知道您的实际表名。

实例

Declare @SQL varchar(max) = '
Select *
 From (
        Select A.ArticleID
              ,D.Description 
              ,B.*
         From  #forecast  A
         Join  #article   D on A.ArticleID=D.ArticleID
         Cross Apply (values (''Week''+left(Week,4),Amount) ) B(Item,Value)
      ) A
 Pivot (max([Value]) 
        For [Item] in (' + Stuff((Select ','+QuoteName('Week'+left(Week,4)) 
                                   From (Select Distinct top 100 [Order],Week From #forecast Order by [Order]  ) A  
                                   For XML Path('')),1,1,'') + ') ) p'
Exec(@SQL);
--Print @SQL

返回

ArticleID   Description Week51  Week52  Week1   Week2   Week3
1           Test        0       150     0       200     0