April 18, 2018 at 11:49 pm
I was curious if there was a similar more efficient way to do a pivot without the normal way of doing it. As with the other one, I have a more complicated one and want to see if there is a better way to do it. I have to now take the unpivot I did and take the normalize table and turn it into a denormalized one for a report.
I have two samples - one that does a one column pivot and one that does a two column one.
The one column pivot:
DECLARE @DailyIncome TABLE
(
VendorId NVARCHAR(10),
IncomeDay NVARCHAR(10),
IncomeAmount INT
)
--drop table DailyIncome
insert into @DailyIncome values ('SPIKE', 'FRI', 100)
insert into @DailyIncome values ('SPIKE', 'MON', 300)
insert into @DailyIncome values ('FREDS', 'SUN', 400)
insert into @DailyIncome values ('SPIKE', 'WED', 500)
insert into @DailyIncome values ('SPIKE', 'TUE', 200)
insert into @DailyIncome values ('JOHNS', 'WED', 900)
insert into @DailyIncome values ('SPIKE', 'FRI', 100)
insert into @DailyIncome values ('JOHNS', 'MON', 300)
insert into @DailyIncome values ('SPIKE', 'SUN', 400)
insert into @DailyIncome values ('JOHNS', 'FRI', 300)
insert into @DailyIncome values ('FREDS', 'TUE', 500)
insert into @DailyIncome values ('FREDS', 'TUE', 200)
insert into @DailyIncome values ('SPIKE', 'MON', 900)
insert into @DailyIncome values ('FREDS', 'FRI', 900)
insert into @DailyIncome values ('FREDS', 'MON', 500)
insert into @DailyIncome values ('JOHNS', 'SUN', 600)
insert into @DailyIncome values ('SPIKE', 'FRI', 300)
insert into @DailyIncome values ('SPIKE', 'WED', 500)
insert into @DailyIncome values ('SPIKE', 'FRI', 300)
insert into @DailyIncome values ('JOHNS', 'THU', 800)
insert into @DailyIncome values ('JOHNS', 'SAT', 800)
insert into @DailyIncome values ('SPIKE', 'TUE', 100)
insert into @DailyIncome values ('SPIKE', 'THU', 300)
insert into @DailyIncome values ('FREDS', 'WED', 500)
insert into @DailyIncome values ('SPIKE', 'SAT', 100)
insert into @DailyIncome values ('FREDS', 'SAT', 500)
insert into @DailyIncome values ('FREDS', 'THU', 800)
insert into @DailyIncome values ('JOHNS', 'TUE', 600)
SELECT * FROM @DailyIncome
SELECT *
FROM @DailyIncome
PIVOT
(
SUM(IncomeAmount)
FOR IncomeDay IN ([MON], [TUE], [WED], [THU], [FRI], [SAT], [SUN])
) AS AvgIncomePerDay
The two column pivot.
Declare @Orders Table (OrderID int,
Product varchar(10),
Quantity int);
-- Insert sample data
Insert Into @Orders
Values (100, 'Cup', 1),
(100, 'Plate', 2),
(101, 'Cup', 1),
(102, 'Cup', 2),
(103, 'Cup', 1),
(103, 'Plate', 2),
(103, 'Glass', 1);
SELECT * FROM @Orders
;With Orders
As
(
SELECT ROW_NUMBER() Over (Partition By OrderID Order By OrderID) As RowID, *
From @Orders
)
--SELECT * FROM Orders
--Select ROW_NUMBER() Over (Partition By OrderID Order By OrderID) As RowID,
-- OrderID,
-- 'Product' + Cast(RowID as varchar) As ProductNum,
-- 'Quantity' + Cast(RowID as varchar) As QuantityNum,
-- Product,
-- Quantity
-- From Orders
Select OrderID As [Order#],
Min(Product1) As Product1,
Min(Quantity1) As Quantity1,
Min(Product2) As Product2,
Min(Quantity2) As Quantity2,
Min(Product3) As Product3,
Min(Quantity3) As Quantity3
From (Select ROW_NUMBER() Over (Partition By OrderID Order By OrderID) As RowID,
OrderID,
'Product' + Cast(RowID as varchar) As ProductNum,
'Quantity' + Cast(RowID as varchar) As QuantityNum,
Product,
Quantity
From Orders) As Pvt
Pivot (Min(Product)
For ProductNum In ([Product1], [Product2], [Product3])) As Pvt1
Pivot (Min(Quantity)
For QuantityNum In ([Quantity1], [Quantity2], [Quantity3])) As Pvt2
Group By OrderID;
Thanks,
Tom
April 19, 2018 at 1:35 pm
tshad - Wednesday, April 18, 2018 11:49 PMI was curious if there was a similar more efficient way to do a pivot without the normal way of doing it. As with the other one, I have a more complicated one and want to see if there is a better way to do it. I have to now take the unpivot I did and take the normalize table and turn it into a denormalized one for a report.
I have two samples - one that does a one column pivot and one that does a two column one.
The one column pivot:
DECLARE @DailyIncome TABLE
(
VendorId NVARCHAR(10),
IncomeDay NVARCHAR(10),
IncomeAmount INT
)--drop table DailyIncome
insert into @DailyIncome values ('SPIKE', 'FRI', 100)
insert into @DailyIncome values ('SPIKE', 'MON', 300)
insert into @DailyIncome values ('FREDS', 'SUN', 400)
insert into @DailyIncome values ('SPIKE', 'WED', 500)
insert into @DailyIncome values ('SPIKE', 'TUE', 200)
insert into @DailyIncome values ('JOHNS', 'WED', 900)
insert into @DailyIncome values ('SPIKE', 'FRI', 100)
insert into @DailyIncome values ('JOHNS', 'MON', 300)
insert into @DailyIncome values ('SPIKE', 'SUN', 400)
insert into @DailyIncome values ('JOHNS', 'FRI', 300)
insert into @DailyIncome values ('FREDS', 'TUE', 500)
insert into @DailyIncome values ('FREDS', 'TUE', 200)
insert into @DailyIncome values ('SPIKE', 'MON', 900)
insert into @DailyIncome values ('FREDS', 'FRI', 900)
insert into @DailyIncome values ('FREDS', 'MON', 500)
insert into @DailyIncome values ('JOHNS', 'SUN', 600)
insert into @DailyIncome values ('SPIKE', 'FRI', 300)
insert into @DailyIncome values ('SPIKE', 'WED', 500)
insert into @DailyIncome values ('SPIKE', 'FRI', 300)
insert into @DailyIncome values ('JOHNS', 'THU', 800)
insert into @DailyIncome values ('JOHNS', 'SAT', 800)
insert into @DailyIncome values ('SPIKE', 'TUE', 100)
insert into @DailyIncome values ('SPIKE', 'THU', 300)
insert into @DailyIncome values ('FREDS', 'WED', 500)
insert into @DailyIncome values ('SPIKE', 'SAT', 100)
insert into @DailyIncome values ('FREDS', 'SAT', 500)
insert into @DailyIncome values ('FREDS', 'THU', 800)
insert into @DailyIncome values ('JOHNS', 'TUE', 600)SELECT * FROM @DailyIncome
SELECT *
FROM @DailyIncome
PIVOT
(
SUM(IncomeAmount)
FOR IncomeDay IN ([MON], [TUE], [WED], [THU], [FRI], [SAT], [SUN])
) AS AvgIncomePerDayThe two column pivot.
Declare @Orders Table (OrderID int,
Product varchar(10),
Quantity int);-- Insert sample data
Insert Into @Orders
Values (100, 'Cup', 1),
(100, 'Plate', 2),
(101, 'Cup', 1),
(102, 'Cup', 2),
(103, 'Cup', 1),
(103, 'Plate', 2),
(103, 'Glass', 1);SELECT * FROM @Orders
;With Orders
As
(
SELECT ROW_NUMBER() Over (Partition By OrderID Order By OrderID) As RowID, *
From @Orders
)
--SELECT * FROM Orders
--Select ROW_NUMBER() Over (Partition By OrderID Order By OrderID) As RowID,
-- OrderID,
-- 'Product' + Cast(RowID as varchar) As ProductNum,
-- 'Quantity' + Cast(RowID as varchar) As QuantityNum,
-- Product,
-- Quantity
-- From Orders
Select OrderID As [Order#],
Min(Product1) As Product1,
Min(Quantity1) As Quantity1,
Min(Product2) As Product2,
Min(Quantity2) As Quantity2,
Min(Product3) As Product3,
Min(Quantity3) As Quantity3
From (Select ROW_NUMBER() Over (Partition By OrderID Order By OrderID) As RowID,
OrderID,
'Product' + Cast(RowID as varchar) As ProductNum,
'Quantity' + Cast(RowID as varchar) As QuantityNum,
Product,
Quantity
From Orders) As Pvt
Pivot (Min(Product)
For ProductNum In ([Product1], [Product2], [Product3])) As Pvt1
Pivot (Min(Quantity)
For QuantityNum In ([Quantity1], [Quantity2], [Quantity3])) As Pvt2
Group By OrderID;Thanks,
Tom
There's an article somewhere on the site about using CROSS TAB queries instead of pivots. Search the Articles portion of this site...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 23, 2018 at 1:21 am
There's an article somewhere on the site about using CROSS TAB queries instead of pivots. Search the Articles portion of this site...
Steve is referring to following articles written by Jeff Moden
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply