April 22, 2012 at 7:31 am
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
April 22, 2012 at 9:06 am
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
April 22, 2012 at 9:33 am
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
April 22, 2012 at 10:00 am
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
April 22, 2012 at 10:36 am
Fitz, please show me. I have no idea what you are talking about.
--Quote me
April 22, 2012 at 1:17 pm
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
April 22, 2012 at 2:10 pm
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
April 22, 2012 at 3:36 pm
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
April 22, 2012 at 4:28 pm
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
Change is inevitable... Change for the better is not.
April 22, 2012 at 5:28 pm
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
April 23, 2012 at 10:32 am
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
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply