SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need Help in SQL Pivot Query


Need Help in SQL Pivot Query

Author
Message
geetha.jasmine
geetha.jasmine
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32943 Visits: 8680
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 on googles mail service
geetha.jasmine
geetha.jasmine
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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
Attachments
PivotQuery.docx (14 views, 19.00 KB)
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32943 Visits: 8680
Please put the stuff up as a TEXT document. I don't download files that can run code. Hehe

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
J Livingston SQL
J Livingston SQL
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12433 Visits: 37638
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

Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52816 Visits: 21195
TheSQLGuru (4/6/2013)
Please put the stuff up as a TEXT document. I don't download files that can run code. Hehe


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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32943 Visits: 8680
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. Hehe


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 on googles mail service
j.miner
j.miner
SSC-Addicted
SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)

Group: General Forum Members
Points: 436 Visits: 358
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
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42742 Visits: 19847
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
j.miner
j.miner
SSC-Addicted
SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)

Group: General Forum Members
Points: 436 Visits: 358
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search