时间戳

首页 > 解决方案 > 如何在sql中使用pivot将两行转换为一列

sql-server - 如何在sql中使用pivot将两行转换为一列

问题描述

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

标签： sql-server

解决方案

你可以试试这个

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;

你可以在这里找到演示

推荐阅读