January 29, 2021 at 5:23 pm
I have a table with the following test data:
Period Item Size Qty Amount
Budget Jeans S 10 100.00
Budget Jeans M 10 100.00
Budget Shirts S 10 150.00
Budget Shirts M 10 150.00
Last Jeans S 1 10.00
Last Jeans M 1 10.00
Last Shirts S 20 200.00
Last Shirts M 20 200.00
Last Pants S 5 50.00
Last Pants M 5 50.00
Curr Jeans S 20 150.00
Curr Jeans M 20 150.00
Curr Shirts S 2 70.00
Curr Shirts M 2 70.00
I want the output to be:
Item Budget Qty Last Qty Curr Qty Budget Amt Last Amt Curr Amount
Jeans 20 2 40 200.00 20.00 300.00
Shirts 20 40 4 300.00 400.00 140.00
Pants 0 10 0 0.00 100.00 0.00
I can do it by creating a derived table with union all of three queries and then a group by on the derived table. But when the data has millions of rows, it takes forever to get the output.
Is there a more efficient way?
January 29, 2021 at 5:43 pm
In the future - please provide sample data in the form of a create statement and insert statement. That can be done using temp tables or a table variable. I have provided this as a table variable to show how this solution works.
This is a cross-tab query - you can review how these are setup and work here: https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%E2%80%93-converting-rows-to-columns-1 and here: https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs
Declare @testTable Table (Period varchar(20), Item varchar(30), [Size] char(1), Qty int, Amount numeric(8,2));
Insert Into @testTable (Period, Item, [Size], Qty, Amount)
Values ('Budget', 'Jeans' , 'S', 10, 100.00)
, ('Budget', 'Jeans' , 'M', 10, 100.00)
, ('Budget', 'Shirts', 'S', 10, 150.00)
, ('Budget', 'Shirts', 'M', 10, 150.00)
, ('Last' , 'Jeans' , 'S', 1, 10.00)
, ('Last' , 'Jeans' , 'M', 1, 10.00)
, ('Last' , 'Shirts', 'S', 20, 200.00)
, ('Last' , 'Shirts', 'M', 20, 200.00)
, ('Last' , 'Pants' , 'S', 5, 50.00)
, ('Last' , 'Pants' , 'M', 5, 50.00)
, ('Curr' , 'Jeans' , 'S', 20, 150.00)
, ('Curr' , 'Jeans' , 'M', 20, 150.00)
, ('Curr' , 'Shirts', 'S', 2, 70.00)
, ('Curr' , 'Shirts', 'M', 2, 70.00);
Select *
From @testTable tt;
--==== Solution
Select tt.Item
, BudgetQty = sum(Case When tt.Period = 'Budget' Then tt.Qty Else 0 End)
, LastQty = sum(Case When tt.Period = 'Last' Then tt.Qty Else 0 End)
, CurrQty = sum(Case When tt.Period = 'Curr' Then tt.Qty Else 0 End)
, BudgetAmount = sum(Case When tt.Period = 'Budget' Then tt.Amount Else 0 End)
, LastAmount = sum(Case When tt.Period = 'Last' Then tt.Amount Else 0 End)
, CurrentAmount = sum(Case When tt.Period = 'Curr' Then tt.Amount Else 0 End)
From @testTable tt
Group By
tt.Item;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 29, 2021 at 7:39 pm
Thank you Jeffrey. I missed out a critical column in my output. Sorry. I want the Period in the output as well.
So once again. This is my test data and my expected output.
Declare @testTable Table (Period varchar(20), Item varchar(30), [Size] char(1), Qty int, Amount numeric(8,2));
Insert Into @testTable (Period, Item, [Size], Qty, Amount)
Values ('Budget', 'Jeans' , 'S', 10, 100.00)
, ('Budget', 'Jeans' , 'M', 10, 100.00)
, ('Budget', 'Shirts', 'S', 10, 150.00)
, ('Budget', 'Shirts', 'M', 10, 150.00)
, ('Budget', 'Pants', 'M', 10, 300.00)
, (‘Q1' , 'Jeans' , 'S', 1, 10.00)
, ('Q1' , 'Jeans' , 'M', 1, 10.00)
, ('Q1' , 'Shirts', 'S', 20, 200.00)
, ('Q2' , 'Shirts', 'M', 20, 200.00)
, ('Q2' , 'Pants' , 'S', 5, 50.00)
, (Q2' , 'Pants' , 'M', 5, 50.00)
, ('Q3' , 'Jeans' , 'S', 20, 150.00)
, ('Q3' , 'Jeans' , 'M', 20, 150.00)
, ('Q3’ , 'Shirts', 'S', 2, 70.00)
, ('Q3' , 'Shirts', 'M', 2, 70.00)
, ('Q4' , 'Jeans' , 'S', 20, 150.00)
, ('Q4' , 'Jeans' , 'M', 20, 150.00)
, ('Q4’ , 'Shirts', 'S', 2, 70.00)
, ('Q4' , 'Shirts', 'M', 2, 70.00)
, ('Q4' , 'Pants' , 'S', 5, 50.00)
, (Q4' , 'Pants' , 'M', 5, 50.00)
Select *
From @testTable tt;
This is my Expected Output:
Period Item Curr Qtr Qty Prev Qtr Qty Budget Qty Curr Qtr Amt Prev Qtr Amt BudgetAmt
Q1 Jeans 2 0 20 20.00 0.00 200.00
Q1 Shirts 20 0 20 200.00 0.00 300.00
Q1 Pants 0 0 10 0.00 0.00 300.00
Q2 Jeans 0 2 20 0.00 20.00 200.00
Q2 Shirts 20 20 20 200.00 200.00 200.00
Q2 Pants 10 0 10 100.00 0.00 300.00
Q3 Jeans 40 0 20 300.00 0.00 200.00
Q3 Shirts 4 20 20 140.00 200.00 200.00
Q3 Pants 0 10 10 0.00 100.00 300.00
Q4 Jeans 40 40 20 300.00 300.00 200.00
Q4 Shirts 4 4 20 140.00 140.00 200.00
Q4 Pants 10 0 10 100.00 0.00 300.00
January 30, 2021 at 3:56 pm
What have you tried so far? This is still just a cross-tab query - you just changed the data.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 30, 2021 at 4:08 pm
Also - it isn't clear how you are counting/summing the data. How do you determine the previous quarter - for example, Q1's previous quarter would be Q4 of the previous year but that data does not exist in the set.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply