declare @tbl table([Level1] varchar(6), [Level2] varchar(...


Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

declare @tbl table([Level1] varchar(6), [Level2] varchar(6), [Level3] varchar(6), [Rec] varchar(6), [Measure] numeric);
insert into @tbl values
 ('cat1_1', 'cat2_1', 'cat3_1', 'rec_1', 5)
,('cat1_2', 'cat2_1', 'cat3_2', 'rec_2', 10)
,('cat1_1', 'cat2_1', 'cat3_3', 'rec_2', 10);

SELECT [Level1], [Level2], [Level3] 
      ,avg([Measure]) OVER (PARTITION BY [Level1]) AS [Average 1]
      ,min([Measure]) OVER (PARTITION BY [Level1]) AS [min 1]
      ,max([Measure]) OVER (PARTITION BY [Level1]) AS [max 1]
      ,avg([Measure]) OVER (PARTITION BY [Level2]) AS [Average 2]
      ,avg([Measure]) OVER (PARTITION BY [Level3]) AS [Average 3]
FROM @tbl;

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results