Using Pivot for multiple columns - SQL Server

Question

I have these tables:

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

Gordon Linoff · Accepted Answer · 2018-08-08 11:17:35Z

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.

answered Aug 8, 2018 at 11:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ryan Gadsdon Over a year ago

The problem is is that my version uses variables that could include 12 months worth of data. So the result set is more dynamic. i dont know if case statements like this would work

Gordon Linoff Over a year ago

@RyanGadsdon . . . It should work as easily as a dynamic pivot.

Ryan Gadsdon · Accepted Answer · 2018-08-08 14:53:44Z

0

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

answered Aug 8, 2018 at 14:53

Ryan Gadsdon

2,4068 gold badges38 silver badges62 bronze badges

Collectives™ on Stack Overflow

Using Pivot for multiple columns - SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related