Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need Help in SQL Pivot Query Expand / Collapse
Author
Message
Posted Friday, April 5, 2013 8:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 5:45 AM
Points: 5, Visits: 24
Hi,
I have a SQL Table in the below structure and am trying to write a PIVOT Query to get the %contribution of Service Request Numbers by Warranty Type. Could anyone pls suggest how do i obtain this?

SR_CountFWEEK prod_DESC WarrantyStatus Orders
1 201402 XPS Desktops IW 1
1 201402 OptiPlex Desktops IW NULL
1 201402 Other Electronics IW NULL
3 201402 Personal Notebooks IW 3
6 201402 XPS Desktops OOW NULL
1 201402 Imaging IW NULL
5 201402 Tablets IW NULL
18 201402 XPS Desktops Unknown NULL
10 201402 Personal Desktops OOW NULL
221 201402 XPS Desktops IW NULL

Pivot Query that i have written is as below:

SELECT * FROM
( SELECT COUNT( [SERVICE REQUEST NUMBER]) SR_Count, FWEEK, prod_DESC,
[WarrantyStatus], SUM([ordercount]) AS Orders
FROM [dbo].[GM_Logs_Wk05] where fweek = '201402'
GROUP BY FWEEK, prod_DESC, [WarrantyStatus],[ordercount]
) AS A
PIVOT
(
sum([SR_Count]) FOR [WarrantyStatus] in
([IW], [OOW]
)
)as b
Output of the above pivot query is as below:

FWEEK prod_DESC Orders IW OOW
201402 Personal Desktops NULL 358 10
201402 Imaging NULL 1 NULL
201402 XPS Desktops 1 1 NULL
201402 OptiPlex Desktops NULL 1 NULL
201402 Other Electronics NULL 1 NULL
201402 Personal NotebooksNULL 1324 45
201402 Tablets NULL 5 NULL
201402 Personal Notebooks 3 3 NULL
201402 Personal Notebooks 1 NULL NULL
201402 XPS Notebooks NULL 231 15
201402 XPS Desktops NULL 221 6

Help I need:
1. How do I get just one row for one product (as for ex: Personal Notebooks is appearing 3times in the above output)
2. Instead of getting the count – is it possible to get % of the column total as in if I draw a pivot on excel below is the output – just wondering how do I convert the value for count to % of total of the respective column
Below is the excel output..(which I do in the excel pivot value field settings – show value as % of column total) which is the similar output i want thru a SQL Pivot query or any other means to acheive this in PIVOT...this query output is what am finally intending to use as a dataset in my SSRS Report.

Prod_desc IW OOW
Personal Notebooks61.76% 57.69%
Personal Desktops 16.67% 12.82%
XPS Notebooks 10.78% 19.23%
XPS Desktops 10.36% 7.69%
Tablets 0.23% 0.00%
Other Electronics 0.05% 0.00%
OptiPlex Desktops 0.05% 0.00%
Imaging 0.05% 0.00%
Grand Total 100.00% 100.00%

Please help.

thanks
Post #1439234
Posted Friday, April 5, 2013 9:28 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:52 PM
Points: 4,404, Visits: 6,264
please read this post (http://www.sqlservercentral.com/articles/SQLServerCentral/66909/) and then reply to this thread with a create table statement, insert statements with your sample data and then formatted output of the desired output. Then we can help you get what you want for this request.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1439289
Posted Saturday, April 6, 2013 2:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 5:45 AM
Points: 5, Visits: 24
Hello,

Thanks for sharing the URL which helps understand how to post the question.

I have rewrriten the question in a word document and has been attached as an attachment name pivotquery.docx

Please review and hope you help find me a solution.

Regards


  Post Attachments 
PivotQuery.docx (7 views, 19.37 KB)
Post #1439552
Posted Saturday, April 6, 2013 5:52 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:52 PM
Points: 4,404, Visits: 6,264
Please put the stuff up as a TEXT document. I don't download files that can run code.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1439573
Posted Saturday, April 6, 2013 10:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:06 AM
Points: 1,917, Visits: 19,596
geetha.jasmine (4/6/2013)
Hello,

Thanks for sharing the URL which helps understand how to post the question.

I have rewrriten the question in a word document and has been attached as an attachment name pivotquery.docx

Please review and hope you help find me a solution.

Regards


a word document is not what is required...please reread the URL posted.

that said...does the following code give you some ideas...


SELECT   FWEEK, prod_DESC, 
cast (100 * SUM(CASE WHEN WarrantyStatus = 'IW' THEN orders ELSE 0 END) / SUM(Orders) as decimal(9,2)) as IWpercent,
cast (100 * SUM(CASE WHEN WarrantyStatus = 'OOW' THEN orders ELSE 0 END) / SUM(Orders) as decimal(9,2)) as OOWpercent,
cast (SUM(CASE WHEN WarrantyStatus = 'IW' THEN orders ELSE 0 END) as INT) AS IW,
cast (SUM(CASE WHEN WarrantyStatus = 'OOW' THEN orders ELSE 0 END) as INT) AS OOW
FROM gm_table
GROUP BY FWEEK, prod_DESC
HAVING (FWEEK = 201404)



______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1439590
Posted Sunday, April 7, 2013 9:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:37 AM
Points: 5,178, Visits: 12,031
TheSQLGuru (4/6/2013)
Please put the stuff up as a TEXT document. I don't download files that can run code.


Word's .docx files cannot run code, as far as I know - that's what .docm files are for.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1439647
Posted Sunday, April 7, 2013 4:15 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:52 PM
Points: 4,404, Visits: 6,264
Phil Parkin (4/7/2013)
TheSQLGuru (4/6/2013)
Please put the stuff up as a TEXT document. I don't download files that can run code.


Word's .docx files cannot run code, as far as I know - that's what .docm files are for.


Learn all kinds of things on SSC.com!!


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1439669
Posted Monday, April 8, 2013 2:26 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:08 PM
Points: 80, Visits: 351

Hi geetha jasmine,

I would not use a pivot since that is used to turn row values into column aggregations such as sums.

Use two common table expressions, a full join, and a bunch of calculations and conversions.

Solution is below creates the following output.

I assume you have a existing database named [test] to play with the solution in.

Sincerely

John


--
-- Results
--

my_fweek my_prod_desc iw_total iw_pct oow_total oow_pct grand_total
201404 Imaging 0 0 1 100 1
201404 Latitude 1 100 0 0 1
201404 Personal Desktops 0 0 2 100 2
201404 Personal Notebooks 5 71.4285714285714 2 28.5714285714286 7
201405 Imaging 0 0 1 100 1
201405 Personal Notebooks 2 66.6666666666667 1 33.3333333333333 3


--
-- Solution
--


-- Use the test database
use test;
go

-- Create the table
Create table gm_table
(
SR_Num INT
, FWEEK INT
, prod_DESC VARCHAR(255)
, WarrantyStatus VARCHAR(255)
, Orders INT
);
go

-- Add data to the table
INSERT INTO GM_TABLE (SR_Num, FWEEK, prod_DESC, WarrantyStatus, Orders) VALUES
(869977621,201404,'Personal Notebooks','IW',1),
(870120681,201404,'Personal Notebooks','IW',1),
(870903077,201405,'Personal Notebooks','IW',1),
(871100687,201405,'Personal Notebooks','IW',1),
(871117976,201404,'Personal Notebooks','IW',1),
(871117976,201404,'Personal Notebooks','IW',1),
(871328699,201404,'Personal Notebooks','IW',1),
(871494818,201404,'Latitude','IW',1),
(872106684,201404,'Personal Desktops','OOW',1),
(872246996,201405,'Imaging','OOW',1),
(872292272,201404,'Imaging','OOW',1),
(872303637,201405,'Personal Notebooks','OOW',1),
(872367703,201404,'Personal Notebooks','OOW',1),
(872430971,201404,'Personal Notebooks','OOW',1),
(872500235,201404,'Personal Desktops','OOW',1);
go

-- Peek at the data
Select * from GM_TABLE
order by FWEEK, prod_DESC, WarrantyStatus
go


--
-- Solve business problem, two cte & full join
--

;
with cte_iw_data
as
(
select fweek, prod_desc, warrantystatus, sum(orders) as iw_total
from gm_table where warrantystatus = 'IW'
group by fweek, prod_desc, warrantystatus
),
cte_oow_data
as
(
select fweek, prod_desc, warrantystatus, sum(orders) as oow_total
from gm_table where warrantystatus = 'OOW'
group by fweek, prod_desc, warrantystatus
)

select
-- pick first non-null week
case
when i.FWEEK is null then o.FWEEK
else i.FWEEK
end as my_fweek,

-- pick first non-null prod desc
case
when i.prod_DESC is null then o.prod_DESC
else i.prod_DESC
end as my_prod_desc,

-- iw total
isnull(iw_total, 0) as iw_total,

-- make iw pct
case
when isnull(iw_total, 0) + isnull(oow_total, 0) = 0 then convert(float, 0)
else
convert(float, isnull(iw_total, 0)) / convert(float, ( isnull(iw_total, 0) + isnull(oow_total, 0) )) * convert(float, 100)
end as iw_pct,

-- oow total
isnull(oow_total, 0) as oow_total,

-- make oow pct
case
when isnull(iw_total, 0) + isnull(oow_total, 0) = 0 then convert(float, 0)
else
convert(float, isnull(oow_total, 0)) / convert(float, ( isnull(iw_total, 0) + isnull(oow_total, 0) )) * convert(float, 100)
end as oow_pct,

-- grand total
isnull(iw_total, 0) + isnull(oow_total, 0) as grand_total

from cte_iw_data i full join cte_oow_data o on i.FWEEK = o.FWEEK and i.prod_DESC = o.prod_DESC



John Miner
Crafty DBA
www.craftydba.com
Post #1440023
Posted Monday, April 8, 2013 4:35 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:43 PM
Points: 3,783, Visits: 8,482
Are you serious j.miner? Take a look at J Livingston code, it's way simpler.
I just would change the having clause for a where clause (to filter the information before grouping it).



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1440061
Posted Tuesday, April 9, 2013 12:33 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:08 PM
Points: 80, Visits: 351
So, you do not like my first solution? I totally agree it was the quickest thing that came off the press.

[color=#FF0000]It is like life, if you spend a couple of minutes more looking at it, a simpler solution can be found.[/color]

How about a filtered roll-up? most of the code is for formatting the results.


--
-- Solve business problem with a roll-up
--

; with cte_rollup as (
select
fweek,
prod_desc,
cast(sum(case when warrantystatus = 'IW' then orders else 0 end) as decimal(9,2)) as iw_total,
cast(sum(case when warrantystatus = 'OOW' then orders else 0 end) as decimal(9,2)) as oow_total,
cast(sum(orders) as decimal(9,2)) as grand_total
from gm_table
group by rollup (fweek, prod_desc)
)
select
fweek, prod_desc, iw_total,
cast((iw_total/ grand_total * 100) as decimal(9,2)) as iw_pct,
oow_total,
cast((oow_total/ grand_total * 100) as decimal(9,2)) as oow_pct
from cte_rollup where prod_desc is not null


John Miner
Crafty DBA
www.craftydba.com
Post #1440497
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse