pivot against two different columns

Question

Post reply

pivot against two different columns

polkadot

SSChampion

Points: 13664
More actions
April 22, 2012 at 7:31 am

#259872

I have a working pivot statement, which pivots againts one column. I'm trying to create some ddl to model it so that I can ask how to do pivot against two columns.
At this point I can't even get the straightforward pivot against one column working. It is simple, and looks right, but it's not working. Desired outcome is to show by application, counts of workitems resolved under respective resolution reasons.
select
application, [deferred], [fixed], [by design]
from
(
select application, resolution_reason, workitem_id
from workitems wi
pivot (count(workitem_id) for resolution_reason in ([deferred], [fixed], [by design])) as pvt
order by application, resolution_reason;
DDL
create table WorkItems
(workitem_id int
, application varchar (20)
, resolution_reason varchar(20)
)
insert into WorkItems
values
(20, 'hearts', 'deferred'),
(21, 'spades', 'deferred'),
(22, 'diamonds', 'fixed'),
(23, 'diamonds', 'by design'),
(24, 'hearts', 'fixed'),
(25, 'hearts', 'fixed'),
(26, 'spades', 'fixed'),
(27, 'joker', 'fixed'),
(28, 'hearts', 'deferred'),
(29, 'spades', 'deferred'),
(30, 'spades', 'fixed');
desired outcome
create table #Result
(
Application varchar(50),
Deferred varchar(50),
Fixed varchar(50),
[By Design] varchar(50)
)
insert #Result
select 'hearts', '2', '2', '' union all
select 'spades', '2', '2', '' union all
select 'diamonds', '', '1', '1' union all
select 'joker', '', '1', ''
please show me flaw so I can progress to next (actual) question, which is how to do pivot against two columns in same table. ( will be adding a Total column, which is a count of workitem_ids).
--Quote me

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Dave Brooking Ten Centuries Points: 1373 More actions · Answer 1

I think your 'straightforward' pivot query should be something like

select application, [deferred], [fixed], [by design]

from WorkItems

PIVOT

(

COUNT(workitem_id)

FOR resolution_reason IN ([deferred], [fixed], [by design])

) AS pvt

order by application

Dave

polkadot SSChampion Points: 13664 More actions · Answer 2

Thanks. I was missing a right parenthesis and alias for the inner. 😛 I was modeling it this way because in real life the WorkItems table has many more columns.

Next, I want to add another column 'Total' which is a count of all workitems per application.

select

application, [deferred], [fixed], [by design], [total]

from

workitems

pivot (count(workitem_id) for resolution_reason in ([deferred], [fixed], [by design])) as pvt

pivot (count(workitem_id) for workitem_id in ([total])) as pvt2

order by application;

needed outcome

create table #Result

(

Application varchar(50),

Deferred varchar(50),

Fixed varchar(50),

[By Design] varchar(50),

Total varchar(50)

)

insert #Result

select 'hearts', '2', '2', '', '4' union all

select 'spades', '2', '2', '', '4' union all

select 'diamonds', '', '1', '1','2' union all

select 'joker', '', '1', '','1'

select * from #Result

--Quote me

Mark Fitzgerald-331224 SSCrazy Eights Points: 8489 More actions · Answer 3

polkadot (4/22/2012)
Thanks. I was missing a right parenthesis and alias for the inner. 😛 I was modeling it this way because in real life the WorkItems table has many more columns.
Next, I want to add another column 'Total' which is a count of all workitems per application.
select
application, [deferred], [fixed], [by design], [total]
from
workitems
pivot (count(workitem_id) for resolution_reason in ([deferred], [fixed], [by design])) as pvt
pivot (count(workitem_id) for workitem_id in ([total])) as pvt2
order by application;
needed outcome
create table #Result
(
Application varchar(50),
Deferred varchar(50),
Fixed varchar(50),
[By Design] varchar(50),
Total varchar(50)
)
insert #Result
select 'hearts', '2', '2', '', '4' union all
select 'spades', '2', '2', '', '4' union all
select 'diamonds', '', '1', '1','2' union all
select 'joker', '', '1', '','1'
select * from #Result

In the final select at the three individual counts to create the total. Remember isnull function as 1+2+null = null.

Fitz

polkadot SSChampion Points: 13664 More actions · Answer 4

Fitz, please show me. I have no idea what you are talking about.

--Quote me

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

polkadot (4/22/2012)
I have a working pivot statement, which pivots againts one column. I'm trying to create some ddl to model it so that I can ask how to do pivot against two columns.
At this point I can't even get the straightforward pivot against one column working. It is simple, and looks right, but it's not working. Desired outcome is to show by application, counts of workitems resolved under respective resolution reasons.
select
application, [deferred], [fixed], [by design]
from
(
select application, resolution_reason, workitem_id
from workitems wi
pivot (count(workitem_id) for resolution_reason in ([deferred], [fixed], [by design])) as pvt
order by application, resolution_reason;
DDL
create table WorkItems
(workitem_id int
, application varchar (20)
, resolution_reason varchar(20)
)
insert into WorkItems
values
(20, 'hearts', 'deferred'),
(21, 'spades', 'deferred'),
(22, 'diamonds', 'fixed'),
(23, 'diamonds', 'by design'),
(24, 'hearts', 'fixed'),
(25, 'hearts', 'fixed'),
(26, 'spades', 'fixed'),
(27, 'joker', 'fixed'),
(28, 'hearts', 'deferred'),
(29, 'spades', 'deferred'),
(30, 'spades', 'fixed');
desired outcome
create table #Result
(
Application varchar(50),
Deferred varchar(50),
Fixed varchar(50),
[By Design] varchar(50)
)
insert #Result
select 'hearts', '2', '2', '' union all
select 'spades', '2', '2', '' union all
select 'diamonds', '', '1', '1' union all
select 'joker', '', '1', ''
please show me flaw so I can progress to next (actual) question, which is how to do pivot against two columns in same table. ( will be adding a Total column, which is a count of workitem_ids).

As I indicated in another thread, your code is not syntactically correct.

Using what you originally posted, this is what I came up with:

create table dbo.WorkItems

(workitem_id int

, app varchar (20)

, resolution_reason varchar(20)

)

insert into WorkItems

values

(20, 'hearts', 'deferred'),

(21, 'spades', 'deferred'),

(22, 'diamonds', 'fixed'),

(23, 'diamonds', 'by design'),

(24, 'hearts', 'fixed'),

(25, 'hearts', 'fixed'),

(26, 'spades', 'fixed'),

(27, 'joker', 'fixed'),

(28, 'hearts', 'deferred'),

(29, 'spades', 'deferred'),

(30, 'spades', 'fixed');

go

select

app, [deferred], [fixed], [by design]

from

(

select app, resolution_reason, workitem_id

from workitems) wi

pivot (count(workitem_id) for resolution_reason in ([deferred], [fixed], [by design])) as pvt

order by app;

go

drop table dbo.WorkItems;

go

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Missed where your syntax error was caught. But as I looked at what you wanted next, does the following work:

create table dbo.WorkItems

(workitem_id int

, app varchar (20)

, resolution_reason varchar(20)

)

insert into WorkItems

values

(20, 'hearts', 'deferred'),

(21, 'spades', 'deferred'),

(22, 'diamonds', 'fixed'),

(23, 'diamonds', 'by design'),

(24, 'hearts', 'fixed'),

(25, 'hearts', 'fixed'),

(26, 'spades', 'fixed'),

(27, 'joker', 'fixed'),

(28, 'hearts', 'deferred'),

(29, 'spades', 'deferred'),

(30, 'spades', 'fixed');

go

select

app, [deferred], [fixed], [by design], appcnt

from

(

select app, resolution_reason, workitem_id, COUNT(*) over (partition by app) as appcnt

from workitems) wi

pivot (count(workitem_id) for resolution_reason in ([deferred], [fixed], [by design])) as pvt

order by app;

go

drop table dbo.WorkItems;

go

polkadot SSChampion Points: 13664 More actions · Answer 7

Lyn, I noted you aggregated within the from clause, necessitating columns be explicitly defined.

COUNT(*) over (partition by application) as total

thank you.

--Quote me

Jeff Moden SSC Guru Points: 1003911 More actions · Answer 8

You might want to consider the alternative if, for nothing else, reasons of performance. Please see the following article.

http://www.sqlservercentral.com/articles/T-SQL/63681/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

polkadot SSChampion Points: 13664 More actions · Answer 9

Yes, I will learn Cross Tabs next. It's on my to do. I've got to get Pivot down and then I am psychologically read to take on Cross Tabs. Thanks Jeff. What was important for me here was that I get the problem solved within pivot framework

--Quote me

Jeff Moden SSC Guru Points: 1003911 More actions · Answer 10

polkadot (4/22/2012)
What was important for me here was that I get the problem solved within pivot framework

Why? Pivot is usually slower and a whole lot more ugly for more complex problems. Is this actually homework and that's why doing it with a PIVOT was important?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)