Using Pivot for multiple columns - SQL Server

Question

I have these tables:

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')

Result:

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

But i was looking for a result that was split out by month like:

e.g.

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

and so on....

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

I have tried a couple of queries but cant seem to get it right. It would be great for any guidance. Thanks

Answer 1

I would suggest simply doing conditional aggregation:

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;

Here is a SQL Fiddle.

Answer 2

WITH Table1 AS
(
select 
ID,
ItemNumber,
CAST(year(DateSold) AS VARCHAR(4)) + ' ' + DATENAME(m, DateSold) AS [DateSold2],
Qty
from ItemOrder
)

select * from Table1
pivot (sum(Qty) for[DateSold2] IN ([2018 August], [2018 July], [2018 June])) as d

More what i was looking for :)

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