sql-server - SQL Server 数据透视和排序

Question

我有一个车间订单表，其中包含商品代码、描述发布日期和所需数量。如何以数据透视表格式进行查询，以便使用 [ReleaseDate] 的年+月按从最早日期到最新日期的排序顺序对结果进行透视。使用年+月作为列。

这是我的查询，但失败了。

 --Declare necessary variables
DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE   @PivotColumns AS NVARCHAR(MAX)

--Get unique values of pivot column  
SELECT   @PivotColumns = COALESCE(@PivotColumns + ',','') + QUOTENAME([YEARMONTH]) 
FROM (SELECT DISTINCT  CONVERT(char(6), cast([releaseddate] as date), 112 ) as [YEARMONTH] FROM [dbo].[ShopOrder]) as PivotQuery

SELECT   @PivotColumns

--Create the dynamic query with all the values for 
--pivot column at runtime

SET   @SQLQuery = 
    N'SELECT ItemCode, ' +   @PivotColumns + '
    FROM [dbo].[ShopOrder] 
    PIVOT( SUM(RequiredQty) 
          FOR [releaseddate] IN (' + @PivotColumns + ')) AS P'

SELECT   @SQLQuery
--Execute dynamic query
EXEC sp_executesql @SQLQuery

这是原始记录

结果查询必须是这样的

Answer 1

在这里，我尝试使用您提供的数据执行数据透视。

查询

Select 
    [ItemCode],
    [Description],
    [2017/8],
    [2017/9]
from
(
select cast(year(ReleasedDate) as nvarchar)+'/'+cast(month(ReleasedDate) as nvarchar) as ReleasedDate,ItemCode,Description,RequiredQty
from shoporder) as PivotData
Pivot
(
sum(RequiredQty) for ReleasedDate in
([2017/8],[2017/9])) as Pivoting
order by ItemCode

输出:

Fiddle