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 |
|-----------+------+-------+--------+

有没有一种方法可以创建一个查询，按照 order 列的顺序为预测表中的每条记录生成一个列。如果可能的话，我该怎么做？

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

Answer 1

只要WEEK号和Order号一致，保持列序是小事。

您可能注意到我使用了#forecast 和#article，因为我不知道您的实际表名。

示例

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