时间戳

首页 > 解决方案 > 对多列使用 Pivot - SQL Server

sql - 对多列使用 Pivot - SQL Server

问题描述

我有这些表：

http://sqlfiddle.com/#!18/b871d/8

create table ItemOrder
(
ID int,
ItemNumber int,
Qty int,
Price int,
Cost int,
DateSold datetime
)

insert into ItemOrder (ID, ItemNumber, Qty, Price, Cost, DateSold)
Values
('1', '145', '5', '50', '25', '08-06-18'),
('2', '145', '5', '50', '25', '07-04-18'),
('3', '145', '5', '50', '25', '06-06-18')

结果：

| ID | ItemNumber |             DateSold | Qty | Price | Cost |
|----|------------|----------------------|-----|-------|------|
|  1 |        145 | 2018-08-06T00:00:00Z |   5 |    50 |   25 |
|  2 |        145 | 2018-07-04T00:00:00Z |   5 |    50 |   25 |
|  3 |        145 | 2018-06-06T00:00:00Z |   5 |    50 |   25 |

但我正在寻找按月划分的结果，例如：

例如

| ID | ItemNumber | Aug-18 Qty | Aug-18 Price | Aug-18 Cost |July-18 Qty|July-18 Price| 
|----|------------|------------|--------------|-------------|
|  1 |        145 |   5        |           50 |          25 |

等等....

select 
ID,
ItemNumber,
DateSold,
(
select ID, ItemNumber, Qty, DateSold
from ItemOrder
) x
PIVOT 
(
SUM(QTY), SUM(Price), SUM(Cost) FOR DateSold in(DateSold1)
) p;

我已经尝试了几个查询，但似乎无法正确处理。任何指导都会很棒。谢谢

标签： sqlsql-serverpivot

解决方案

我建议简单地进行条件聚合：

select id, itemnumber,
       sum(case when datesold >= '2018-08-01' and datesold < '2018-09-01' then qty else 0 end) as qty_201808,
       sum(case when datesold >= '2018-08-01' and datesold < '2018-09-01' then price else 0 end) as price_201808,
       sum(case when datesold >= '2018-07-01' and datesold < '2018-08-01' then qty else 0 end) as qty_201807,
       sum(case when datesold >= '2018-07-01' and datesold < '2018-08-01' then price else 0 end) as price_201807
from itemorder
group by id, itemnumber
order by id, itemnumber;

这是一个 SQL 小提琴。

推荐阅读