I am trying to generate the following results set from the raw data below:
Raw Data (SourceTable):
Desired Results set:
Essentially I want the MAX value partitioned over each UserID and Store and filtered by Product, with a column for only Apple values and a column for only Grape values. The only way I've been able to achieve this is to create separate CTE tables for each new column that partition and filter the data and then join them together but this is causing performance issues.
WITH cte AS
(
SELECT *
FROM SourceTable]), cte2 AS
(
SELECT *,
Max([Value]) OVER (partition BY [UserID], [Store]) AS max_value_by_apples
FROM cte
WHERE [Product] = 'Apples'), cte3 AS
(
SELECT *,
Max([Value]) OVER (partition BY [UserID], [Store]) AS max_value_by_grapes
FROM cte
WHERE [Product] = 'Grapes')
SELECT a.*,
b.max_value_by_apples,
c.max_value_by_grapes
FROM cte a
LEFT JOIN cte2 b
ON a.[UserID] = b.[UserID]
AND a.[Store] = b.[Store]
LEFT JOIN cte3 c
ON a.[UserID] = c.[UserID]
AND a.[Store] = c.[Store]
Any help would be greatly appreciated.
query
SELECT *,
Max([Value]) OVER (partition BY [UserID], [Store]) AS max_value_by_grapes
FROM datatable
WHERE [Product] = 'Grapes'
is like
select *
,max(case when product='Apples' then value end)over(partition by userid,store) max_value_by_apples
FROM datatable
OR expression max(case when product='Apples' then value end)
can be used instead filter (or as filter) where product='Apples'
in window function.
See example
select *
,max(case when product='Apples' then value end)over(partition by userid,store) max_value_by_apples
,max(case when product='Grapes' then value end)over(partition by userid,store) max_value_by_grapes
from data