tsql - SQL Server : Pivot with grouping -

i trying have query pivot data , working fine except noticed data issue further downstream our application development. noticed since having max because of pivot requiring aggregate function, literally maxing value without taking consideration columns not distinctly same. new pivoting may doing wrong result want. or direction appreciated.

query:

select      @cols = stuff((select ',' + quotename(proto_name)                    #temp_rackplan                    group proto_name                    order proto_name                    xml path(''),type).value('.', 'nvarchar(max)'), 1, 1, '') set @query = 'select                   dept_catg_grp_desc [category group]                   ,dept_category_desc [category]                   ,fineline_desc [fineline]                   ,supplier                   ,ty_cust_choice_qty [cc]                   ,season_code [sc]                   ,set_strategy_desc [set strategy]                   ,ty_landed_cost_amt [landed cost]                   ,ty_freight_factor_pct [ff%]                   ,ty_cost_w_ff [cost w/ff]                   ,ty_retail_amt [retail $]                   ,mu_pct [mu%] ,in_store_wm_yr_wk_id [in str wk] ,ty_start_wm_yr_wk_id [start wk] ,ty_end_wm_yr_wk_id [end wk] ,ty_md_wm_yr_wk_id [md wk] ,fixture_group_desc [fixture] ,brand ,' + @cols +  '  ( select     dept_catg_grp_desc     ,dept_category_desc     ,fineline_desc     ,supplier     ,ty_cust_choice_qty     ,season_code     ,set_strategy_desc     ,ty_landed_cost_amt     ,ty_freight_factor_pct     ,ty_cost_w_ff     ,ty_retail_amt     ,mu_pct     ,in_store_wm_yr_wk_id     ,ty_start_wm_yr_wk_id     ,ty_end_wm_yr_wk_id     ,ty_md_wm_yr_wk_id     ,fixture_group_desc     ,brand     ,proto_name     ,trgt_rack_cnt     #temp_rackplan ) x pivot  (     max(trgt_rack_cnt)     proto_name in (' + @cols + ') ) p ' 

query result:

catgrp  category  fineline  set strategy  fixture  proto  proto b  proto c ---------------------------------------------------------------------------- shoes   mens      254       10-50         4way     2        1        1 shoes   mens      254       10-50         h-rack   2        1        1 shoes   mens      254       60-90         4way     2        1        1 shoes   mens      254       60-90         h-rack   2        1        1 shoes   mens      2920      10-50         4way     2        1        1 shoes   mens      2920      10-50         h-rack   2        1        1 shoes   mens      2920      60-90         4way     2        1        1 shoes   mens      2920      60-90         h-rack   2        1        1 

desired result (matches database):

catgrp  category  fineline  set strategy  fixture  proto  proto b  proto c ---------------------------------------------------------------------------- shoes   mens      254       10-50         4way     2        1        1 shoes   mens      254       10-50         h-rack   1        1        1 shoes   mens      254       60-90         4way     1        1        1 shoes   mens      254       60-90         h-rack   1        1        1 shoes   mens      2920      10-50         4way     1        1        1 shoes   mens      2920      10-50         h-rack   1        1        1 shoes   mens      2920      60-90         4way     2        1        1 shoes   mens      2920      60-90         h-rack   1        1        1 

based on research not able use pivot function per requirement of report. here workaround created outputs proper data (rough draft)

declare @ps_id nvarchar(5) = 2519 declare @cols nvarchar(max) declare @curproto nvarchar(50) declare @loopcnter int = 0 declare @protocnt int = (select distinct count(distinct proto_name) apparel_planning_reporting.dbo.plan_proto planning_session_id = @ps_id) declare @looptbl table ( id int, proto_name nvarchar(50) )  create table #temploop(fineline int, strategy_zone_group_cd int)  --insert initial records proto table insert @looptbl select distinct 0 id, proto_name apparel_planning_reporting.dbo.plan_proto planning_session_id = @ps_id  --generate record id update @looptbl set @loopcnter = id = @loopcnter + 1  declare @sqlexe nvarchar(max) --loop generate report set @loopcnter = 1 while(@loopcnter <= @protocnt) begin        set @curproto = (select proto_name @looptbl id = @loopcnter)        set @sqlexe = 'alter table #temploop add [' + @curproto + '] decimal(18,2)'        exec (@sqlexe)        set @sqlexe = 'insert #temploop (fineline, strategy_zone_group_cd,['+@curproto+']) select pras.fineline_nbr, pras.strategy_zone_group_cd, pras.trgt_rack_cnt apparel_planning_reporting.dbo.plan_rack_alloc_store pras, apparel_planning_reporting.dbo.plan_proto pp coalesce(pras.planning_session_id, pp.planning_session_id) =' + @ps_id + ' , pras.str_collection_id = pp.plan_proto_id , pp.proto_name = '''+@curproto+''' option(maxdop 12)'        exec (@sqlexe)        set @loopcnter = @loopcnter + 1 end --select * #temploop  select @cols = stuff((             select ',max(' + quotename(proto_name) + ') ' + quotename(proto_name)             @looptbl             group proto_name             order proto_name             xml path('')                 ,type             ).value('.', 'nvarchar(max)'), 1, 1, '')  set @sqlexe = ' select     fineline     ,strategy_zone_group_cd     ,'+@cols+' #temploop group fineline, strategy_zone_group_cd ' exec(@sqlexe)  drop table #temploop