如何在sql中使用透视将两行转换为一列

Question

我想使用数据库中的 PIVOT 函数将两列转换为一行SQL。但我面临着问题。我可以将 1 列转换为 1 行，但不能将 2 列转换为 1 行。

Answer 1

你可以

试试这个

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;

您可以在此处找到演示