Using Pivot in SQL

Question

Post reply

Using Pivot in SQL

Mysterio

Old Hand

Points: 360
More actions
August 7, 2020 at 5:38 am

#3778082

I am trying to create a query from this table: above is my table, and below is what I need my query to show.
I tried many things: first, I tried using UNIONs, but it did not allow me to sum/average. Then I tried to experiment with the PIVOT function, but again, the issue is the percent requirement. One thing that I did not want to do is to create a table and then storing the information there. I think that this can be resolved by a query, so that is not a desirable solution.
% of total is calculated by getting total orders for the Product Category. Which in the example is 001, and then dividing total order for the subcate region over the total. Many, many thanks in advance!

Viewing 15 posts - 1 through 15 (of 26 total)

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

pietlinden SSC Guru Points: 63512 More actions · Answer 1

use a stored procedure, and create a variable in it, say @Total and ...

SELECT @AllSalesAmount = 1.0 * SUM(Sales[Amount]);

Then divide your sum by that in your pivot.

Ken McKelvey SSCoach Points: 18859 More actions · Answer 2

It looks like you actually need to unpivot. This can be done by joining to some numbers, to increase the rows, and then use a CASE.

If you can be bothered to post consumable test data someone might even give you an example.

Mysterio Old Hand Points: 360 More actions · Answer 3

Use of SP is not allowed since we are analysts and without exec permission.

Sorry didn't know that would be useful. Here.


CREATE TABLE [dbo].[ProductOrders](
[ProductCategory] [varchar](5) NULL,
[ProductSubCategory] [varchar](5) NULL,
[Total Orders Placed in North America] [int] NULL,
[Total Orders Placed in South America] [int] NULL,
[Total Orders Placed in North Africa] [int] NULL,
[Total Orders Placed in South Africa] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[ProductOrders] ([ProductCategory], [ProductSubCategory], [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa]) VALUES (N'001', N'A', 2, 5, 12, 20)
GO
INSERT [dbo].[ProductOrders] ([ProductCategory], [ProductSubCategory], [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa]) VALUES (N'001', N'B', 5, 5, 14, 21)
GO
INSERT [dbo].[ProductOrders] ([ProductCategory], [ProductSubCategory], [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa]) VALUES (N'001', N'C', 1, 7, 15, 17)
GO
INSERT [dbo].[ProductOrders] ([ProductCategory], [ProductSubCategory], [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa]) VALUES (N'002', N'A', 6, 8, 11, 16)
GO
INSERT [dbo].[ProductOrders] ([ProductCategory], [ProductSubCategory], [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa]) VALUES (N'002', N'B', 3, 9, 13, 19)
GO
INSERT [dbo].[ProductOrders] ([ProductCategory], [ProductSubCategory], [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa]) VALUES (N'002', N'C', 2, 6, 14, 0)
GO
INSERT [dbo].[ProductOrders] ([ProductCategory], [ProductSubCategory], [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa]) VALUES (N'002', N'D', 8, 8, 13, 21)
GO
INSERT [dbo].[ProductOrders] ([ProductCategory], [ProductSubCategory], [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa]) VALUES (N'003', N'A', 2, 9, 10, 18)
GO
INSERT [dbo].[ProductOrders] ([ProductCategory], [ProductSubCategory], [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa]) VALUES (N'003', N'B', 9, 0, 0, 29)
GO

Ken McKelvey SSCoach Points: 18859 More actions · Answer 4

Making the test rig more user friendly:

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE #PO
(
ProductCategory varchar(5) NOT NULL
,ProductSubCategory varchar(5) NOT NULL
,[Total Orders Placed in North America] [int] NULL
,[Total Orders Placed in South America] [int] NULL
,[Total Orders Placed in North Africa] [int] NULL
,[Total Orders Placed in South Africa] [int] NULL
,PRIMARY KEY (ProductCategory, ProductSubCategory)
);
GO
INSERT INTO #PO
VALUES ('001', 'A', 2, 5, 12, 20)
,('001', 'B', 5, 5, 14, 21)
,('001', 'C', 1, 7, 15, 17)
,('002', 'A', 6, 8, 11, 16)
,('002', 'B', 3, 9, 13, 19)
,('002', 'C', 2, 6, 14, 0)
,('002', 'D', 8, 8, 13, 21)
,('003', 'A', 2, 9, 10, 18)
,('003', 'B', 9, 0, 0, 29);
GO

This should get you started:

WITH NormalizedPO
AS
(
SELECT PO.ProductCategory, PO.ProductSubCategory
,CASE N.N
WHEN 1 THEN 'North America'
WHEN 2 THEN 'South America'
WHEN 3 THEN 'North Africa'
WHEN 4 THEN 'North Africa'
END AS Region
,CASE N.N
WHEN 1 THEN [Total Orders Placed in North America]
WHEN 2 THEN [Total Orders Placed in South America]
WHEN 3 THEN [Total Orders Placed in North Africa]
WHEN 4 THEN [Total Orders Placed in South Africa]
END AS OrdersPlaced
FROM #PO PO
CROSS JOIN (SELECT V.N FROM (VALUES (1),(2),(3),(4)) V(N)) N
)
SELECT ProductCategory, ProductSubCategory, Region, OrdersPlaced
-- Round this as you want. It is unlikely to add up to 100 without weighting.
,OrdersPlaced * 100.0 / SUM(OrdersPlaced) OVER (PARTITION BY ProductCategory) AS PercentPlaced
FROM NormalizedPO;

Steve Collins SSCrazy Points: 2174 More actions · Answer 5

When CROSS JOIN with the tally table the resulting ordering may not align with Region. It's not known which region will be 1 or 2 or ... unless joined back to something. UNION ALL works.

;with unpvt_cte(ProductCategory, ProductSubCategory, Region, OrdersPlaced) as (
    select ProductCategory, ProductSubCategory, 'North America', [Total Orders Placed in North America] from #PO union all
    select ProductCategory, ProductSubCategory, 'South America', [Total Orders Placed in South America] from #PO union all
    select ProductCategory, ProductSubCategory, 'North Africa', [Total Orders Placed in North Africa] from #PO union all
    select ProductCategory, ProductSubCategory, 'South Africa', [Total Orders Placed in South Africa] from #PO)
select
  ProductCategory, ProductSubCategory, Region, OrdersPlaced, 
  (OrdersPlaced*100.0/SUM(OrdersPlaced) over (partition by ProductCategory)) PercentPlaced;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Ken McKelvey SSCoach Points: 18859 More actions · Answer 6

The tally table works, as long as the use of the numbers is consistent, and scans the table once.

UNION ALL scans the table four times.

Steve Collins SSCrazy Points: 2174 More actions · Answer 7

SSC

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2174 More actions · Answer 8

The bottom grouping is the code with tally ordered by 1,2,3. The top is UNION ALL with same ordering

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2174 More actions · Answer 9

Wait a sec. North Africa is in the code twice!

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2174 More actions · Answer 10

Ok ok I changed the decode to 'South Africa' and now it does work. Misdiagnosed due to misleading code? Yea, if it scans once and there's no dependency then it should be consistently applied?

WITH NormalizedPO
AS
(
SELECT PO.ProductCategory, PO.ProductSubCategory
,CASE N.N
WHEN 1 THEN 'North America'
WHEN 2 THEN 'South America'
WHEN 3 THEN 'North Africa'
WHEN 4 THEN 'South Africa'
END AS Region
,CASE N.N
WHEN 1 THEN [Total Orders Placed in North America]
WHEN 2 THEN [Total Orders Placed in South America]
WHEN 3 THEN [Total Orders Placed in North Africa]
WHEN 4 THEN [Total Orders Placed in South Africa]
END AS OrdersPlaced
FROM #PO PO
CROSS JOIN (SELECT V.N FROM (VALUES (1),(2),(3),(4)) V(N)) N
)
SELECT ProductCategory, ProductSubCategory, Region, OrdersPlaced
-- Round this as you want. It is unlikely to add up to 100 without weighting.
,OrdersPlaced * 100.0 / SUM(OrdersPlaced) OVER (PARTITION BY ProductCategory) AS PercentPlaced
FROM NormalizedPO
order by 1,2,3;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

sgmunson SSC Guru Points: 110640 More actions · Answer 11

Why not just UNPIVOT, as shown here in a CTE:

CREATE TABLE dbo.ProductOrders (
ProductCategory varchar(5) NULL,
ProductSubCategory varchar(5) NULL,
[Total Orders Placed in North America] int NULL,
[Total Orders Placed in South America] int NULL,
[Total Orders Placed in North Africa] int NULL,
[Total Orders Placed in South Africa] int NULL
);
INSERT INTO dbo.ProductOrders (ProductCategory, ProductSubCategory, [Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa])
VALUES('001', 'A', 2, 5, 12, 20),
('001', 'B', 5, 5, 14, 21),
('001', 'C', 1, 7, 15, 17),
('002', 'A', 6, 8, 11, 16),
('002', 'B', 3, 9, 13, 19),
('002', 'C', 2, 6, 14, 0),
('002', 'D', 8, 8, 13, 21),
('003', 'A', 2, 9, 10, 18),
('003', 'B', 9, 0, 0, 29);

SET STATISTICS IO ON;

WITH UNPIVOTED AS (

SELECT
REPLACE(RegionName, 'Total Orders Placed in ', '') AS Region,
ProductCategory,
ProductSubCategory,
Orders
FROM dbo.ProductOrders AS PO
UNPIVOT (Orders FOR RegionName IN ([Total Orders Placed in North America], [Total Orders Placed in South America], [Total Orders Placed in North Africa], [Total Orders Placed in South Africa])) AS UPVT
)
SELECT
ProductCategory,
ProductSubCategory,
Region,
Orders,
Orders  * 1.0 / SUM(Orders) OVER (PARTITION BY ProductCategory) AS PercentageOfOrders
FROM UNPIVOTED
ORDER BY
ProductCategory,
Region,
ProductSubCategory;

SET STATISTICS IO OFF;

DROP TABLE dbo.ProductOrders;

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Steve Collins SSCrazy Points: 2174 More actions · Answer 12

sgmunson wrote:

Why not just UNPIVOT, as shown here in a CTE:

Both work. The tally based is probably more efficient depending on scale. It didn't occur to use a tally table the way Ken did. I'm really liking that solution now. Without the CASE WHEN's and just using iif's

;with unpvt_cte(ProductCategory, ProductSubCategory, Region, OrdersPlaced) as (
select po.ProductCategory, po.ProductSubCategory,
           iif(n.n=1, 'North America', 
           iif(n.n=2, 'South America',
   iif(n.n=3, 'North Africa', 
           iif(n.n=4, 'South Africa', null)))),
           iif(n.n=1, [Total Orders Placed in North America], 
           iif(n.n=2, [Total Orders Placed in South America],
   iif(n.n=3, [Total Orders Placed in North Africa], 
           iif(n.n=4, [Total Orders Placed in South Africa], 0))))
from #po po cross join dbo.fnTally(1, 4) n)
select
  ProductCategory, ProductSubCategory, Region, OrdersPlaced,
  (OrdersPlaced*100.0/SUM(OrdersPlaced) over (partition by ProductCategory)) PercentPlaced
from unpvt_cte;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

sgmunson SSC Guru Points: 110640 More actions · Answer 13

Steve Collins wrote:

sgmunson wrote:

Why not just UNPIVOT, as shown here in a CTE:

Both work. The tally based is probably more efficient depending on scale. It didn't occur to use a tally table the way Ken did. I'm really liking that solution now. Without the CASE WHEN's and just using iif's

;with unpvt_cte(ProductCategory, ProductSubCategory, Region, OrdersPlaced) as (
select po.ProductCategory, po.ProductSubCategory,
           iif(n.n=1, 'North America', 
           iif(n.n=2, 'South America',
   iif(n.n=3, 'North Africa', 
           iif(n.n=4, 'South Africa', null)))),
           iif(n.n=1, [Total Orders Placed in North America], 
           iif(n.n=2, [Total Orders Placed in South America],
   iif(n.n=3, [Total Orders Placed in North Africa], 
           iif(n.n=4, [Total Orders Placed in South Africa], 0))))
from #po po cross join dbo.fnTally(1, 4) n)
select
  ProductCategory, ProductSubCategory, Region, OrdersPlaced,
  (OrdersPlaced*100.0/SUM(OrdersPlaced) over (partition by ProductCategory)) PercentPlaced
from unpvt_cte;

True, but I'm somewhat partial to PIVOT and UNPIVOT as they are fairly easy to code, and it's not that difficult to make them dynamic with dynamic SQL. Part of that partiality is due to having read up on those "then new" features of SQL 2005 just the night before I was to start work on my very 1st IT contract, in a book on SQL 2005 that I bought for that purpose, not having had any prior exposure to that version, and then having a working query that used both of them on my first day on the job.

This reply was modified 4 years, 4 months ago by sgmunson. Reason: Oops... hit button by accident before I was done typing

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ScottPletcher SSC Guru Points: 101126 More actions · Answer 14

Gotta admit, I'm not a fan of PIVOT nor UNPIVOT. I just don't care for the syntax / approach. It seems counter-intuitive to me, naturally YMMV.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".