Need Help on SQL Pivot Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Need Help on SQL Pivot Query

Author

Topic

geethachetan
Starting Member

5 Posts

Posted - 2013-04-05 : 10:00:48

Hello,
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_Count FWEEK 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 Notebooks NULL 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 of 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 Notebooks 61.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%

Please help.

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-05 : 15:10:17

Can you try this - if it does not work, can you post the data in consumable format (i.e., something that someone can copy and paste to their SSMS window and run. See here for how to do that http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx )

SELECT FWEEK,prod_DESC,
	SUM(IW) over(partition by prod_DESC)*100.0/SUM(IW) as IW,
	SUM(OOW) over(partition by prod_DESC)*100.0/SUM(OOW) as OOW 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

geethachetan
Starting Member

5 Posts

Posted - 2013-04-06 : 03:56:25

Thanks for your response. The output is not yet close to what i am wanting. hence as you suggested, re-posting the question..as below :

Create table gm_table
(
SR_Num INT
, FWEEK INT
, prod_DESC VARCHAR(255)
, WarrantyStatus VARCHAR(255)
, Orders INT
)

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)

From the above table (gm_table) that i have created what i need is the count of SR_Num (%of row) by WarrantyType & SumofOrders

To acheive this i wrote a Pivot query which is not giving the desired result:

SELECT * FROM
( SELECT COUNT( [SR_NUM]) SR_Count, FWEEK, prod_DESC,
[WarrantyStatus], SUM([orders]) AS TotalOrders
FROM GM_TABLE where fweek = '201404'
GROUP BY FWEEK, prod_DESC, [WarrantyStatus],[orders]
) AS A
PIVOT
(
sum([SR_Count]) FOR [WarrantyStatus] in
([IW], [OOW]
)
)as b

Later, on the same dataset i tried the query suggested by you:

SELECT FWEEK,prod_DESC,
SUM(IW) over(partition by prod_DESC)*100.0/SUM(IW) as IW,
SUM(OOW) over(partition by prod_DESC)*100.0/SUM(OOW) as OOW,
Orders FROM
( SELECT COUNT( [SR_NUM]) SR_Count, FWEEK, prod_DESC,
[WarrantyStatus], SUM([orders]) AS Orders
FROM gm_table where fweek = '201404'
GROUP BY FWEEK, prod_DESC, [WarrantyStatus],[orders]
) AS A
PIVOT
(
sum([SR_Count]) FOR [WarrantyStatus] in
([IW], [OOW]
)
)as b
group by FWEEK,prod_DESC, b.iw, b.oow, b.orders

Output is still not what am looking at.

I need help in building a query which can show output as below (This is the desired output which i got from excel):

Fweek Prod_Desc IW - Count of SR_NUM OOW - Count of SR_NUM IW - Sum of Orders OOW - Sum of Orders
201404 Imaging 0.00% 100.00% 1
201404 Latitude 100.00% 0.00% 1
201404 Personal Desktops 0.00% 100.00% 2
201404 Personal Notebooks 71.43% 28.57% 5 2

sorry dont know how to post a table (output) in the correct table format..

Any help will be much appreciated. Thanks

Geetha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 01:32:14

sounds like this to me


SELECT FWEEK,prod_DESC,
SUM(CASE WHEN WarrantyStatus = 'OOW' THEN 1 ELSE 0 END)*1.0/SUM(1) AS CountOOW,
SUM(CASE WHEN WarrantyStatus <> 'OOW' THEN 1 ELSE 0 END)*1.0/SUM(1) AS CountOrders,
SUM(CASE WHEN WarrantyStatus = 'OOW' THEN 1 ELSE 0 END) AS SumOOW,
SUM(CASE WHEN WarrantyStatus <> 'OOW' THEN 1 ELSE 0 END) AS SumOrders
FROM gm_table 
GROUP BY FWEEK,prod_DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources