Hi Community,
I have a table which needs to be unpivoted so, that I can have weekly sales & Volume in a row for a particular week.
I had a crack using unpivot & union however, I get 2 rows. Can't get my head around as to how I can merge Week 1 Sales & Volume to populate in a single row. Attached desired output for reference.
Can someone please help? Thanks,
-- Create Table
if exists
(select * from dbo.sysobjects where id = object_id(N'[Extra_Data]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Extra_Data]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Extra_Data]
(
[Load_date] [datetime] NULL,
[AGG_TYPE] [nvarchar](255) NULL,
[PRODUCT_GROUP] [nvarchar](255) NULL,
[PRODUCT_NAME] [nvarchar](255) NULL,
[PRODUCT_CODE] [nvarchar](255) NULL,
[FORMAT_NAME] [nvarchar](255) NULL,
[STORE_REGION] [nvarchar](255) NULL,
[STORE_NAME] [nvarchar](255) NULL,
[STORE_CODE] [nvarchar](255) NULL,
[Week1_Sales] [float] NULL,
[Week2_Sales] [float] NULL,
[Week1_Volume] [float] NULL,
[Week2_Volume] [float] NULL
) ON [PRIMARY]
INSERT INTO dbo.[Extra_data] SELECT '2021-08-02', 'Weekly', 'Butter', 'Good Butter' , '1001', '500g Pack Size', 'North', 'ABC Supermarket', '5000', '55.38', '88.98', '11.02', '15.68'
INSERT INTO dbo.[Extra_data] SELECT '2021-08-02', 'Weekly', 'Butter', 'Good Butter' , '1001', '500g Pack Size', 'North', 'Best Dairy Store', '4000', '22.08', '88.98', '12.02', '15.68'
INSERT INTO dbo.[Extra_data] SELECT '2021-08-02', 'Weekly', 'Butter', 'Good Butter' , '1001', '500g Pack Size', 'North', 'Best Dairy Store', '4000', '16.31', '88.98', '13.02', '15.68'
INSERT INTO dbo.[Extra_data] SELECT '2021-08-02', 'Weekly', 'Butter', 'Value Butter' , '1001', '500g Pack Size', 'North', 'ABC Supermarket', '5000', '57.31', '26.26', '14.02', '15.68'
INSERT INTO dbo.[Extra_data] SELECT '2021-08-02', 'Weekly', 'Butter', 'Value Butter' , '1001', '500g Pack Size', 'North', 'ABC Supermarket', '5000', '58.38', '26.26', '15.02', '15.68'
INSERT INTO dbo.[Extra_data] SELECT '2021-08-02', 'Weekly', 'Butter', 'Value Butter' , '1001', '500g Pack Size', 'North', 'ABC Supermarket', '5000', '59.68', '26.26', '12.02', '15.68'
INSERT INTO dbo.[Extra_data] SELECT '2021-08-02', 'Weekly', 'Butter', 'Value Butter' , '1001', '500g Pack Size', 'North', 'ABC Supermarket', '5000', '45.98', '26.26', '11.02', '15.68'
INSERT INTO dbo.[Extra_data] SELECT '2021-08-02', 'Weekly', 'Butter', 'Good Butter' , '1001', '500g Pack Size', 'North', 'ABC Supermarket', '5000', '56.98', '18.98', '90.02', '15.68'
INSERT INTO dbo.[Extra_data] SELECT '2021-08-02', 'Weekly', 'Butter', 'Good Butter' , '1001', '500g Pack Size', 'North', 'ABC Supermarket', '5000', '55.38', '28.98', '20.02', '15.68'
INSERT INTO dbo.[Extra_data] SELECT '2021-08-02', 'Weekly', 'Butter', 'Good Butter' , '1001', '500g Pack Size', 'North', 'ABC Supermarket', '5000', '55.38', '38.98', '10.02', '15.68'
GO;
-- Query
SELECT DISTINCT
Load_date,AGG_TYPE,PRODUCT_GROUP,PRODUCT_NAME,PRODUCT_CODE,FORMAT_NAME, STORE_REGION,STORE_NAME,STORE_CODE, Week_Number, Sales, '' AS Volume
FROM [dbo].[Extra_Data] p
UNPIVOT
(
Sales FOR Week_Number IN ([Week1_Sales],[Week2_Sales])
)
AS Upt
UNION ALL
SELECT DISTINCT
Load_date,AGG_TYPE,PRODUCT_GROUP,PRODUCT_NAME,PRODUCT_CODE,FORMAT_NAME, STORE_REGION,STORE_NAME,STORE_CODE,Week_Number, '' AS Sales, Volume
FROM [dbo].[Extra_Data] p
UNPIVOT
(
Volume FOR Week_Number IN ([Week1_Volume],[Week2_Volume])
)
AS Upt1
ORDER BY Store_Code ASC
August 8, 2021 at 4:07 pm
How are week_1 and week_2 related to Load_Date?
This looks like you're making it a whole lot more complicated than it needs to be. This is my start at it, but since I don't know what week 1 and week 2 mean, I'm not totally sure... If it just means you add 0 or 7 days to the date, then it's pretty simple.
Here's my start at it:
/* Get weekly sales and volume in a single row for a particular week */
SELECT z.PRODUCT_CODE
, z.saleweek
, Sales = sum(bothweeksales)
, Volume = sum(bothweekvolume)
FROM
(
SELECT Product_code,
format_name,
saleweek = datepart(week,load_date),
bothWeekSales = week1_sales + week2_sales,
bothWeekVolume = week1_volume + week2_volume
FROM dbo.extra_data
) z
GROUP BY z.PRODUCT_CODE
, z.saleweek;
August 8, 2021 at 10:00 pm
Hi @pietlinden
Appreciate your time looking into this.
2. Now, the reason I want to use unpivot is because I want the Week 1 & Week 2 to come as a row rather than a column. When I transpose Week1/Week 2 to come as a row _ Sales or _volume should disappear & I want to get the results below. The data set is further going to be used by Analysts and this is what the requirement is.
Does this make sense?
Thanks again!
SELECT u.AGG_TYPE
, u.PRODUCT_GROUP
, u.PRODUCT_NAME
,u.product_code
,u.FORMAT_NAME
,u.store_region
,u.store_code
, u.weekno
, SUM(u.sales) AS TotalSales
, SUM(u.volume) AS TotalVolume
FROM
(SELECT d.agg_type,
d.PRODUCT_GROUP,
d.PRODUCT_NAME,
d.product_code,
d.FORMAT_NAME,
d.store_region,
d.store_code,
d.week1_sales AS Sales,
d.Week1_Volume AS Volume,
1 as weekno
FROM dbo.extra_data d
UNION ALL
SELECT d.agg_type,
d.PRODUCT_GROUP,
d.PRODUCT_NAME,
d.product_code,
d.FORMAT_NAME,
d.store_region,
d.store_code,
d.week2_sales,
d.Week2_Volume,
2 as weekno
FROM dbo.extra_data d) u
GROUP BY u.AGG_TYPE
, u.PRODUCT_GROUP
, u.PRODUCT_NAME
, u.product_code
, u.FORMAT_NAME
, u.store_region
, u.store_code
, u.weekno;
August 9, 2021 at 12:32 am
That's great! No unpivot required.
Thanks heaps pietlinden! 🙂
August 9, 2021 at 12:50 am
Oh good, because I was going to add that (as far as I know) there's no way to pivot the data you have without explicitly adding a column for week number.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply