Pivot but preserve all rows on Aggregate column

• getsaby

  Old Hand

  Points: 341

  More actions

  December 10, 2025 at 5:19 pm

  #4698495

  Hello

  Need help in pivoting this data set, the Pivot takes MIN/MAX on a column and creates only row, my goal is to preserve all rows. We need to pivot on DSTRCT_CODE which will have only two values {BBBB/CCCC}  on value of column PREF_PART_IND.

  so for below sample data :

  with q_data as
  (
  select 'BBBB' DSTRCT_CODE,'001201624777' STOCK_CODE,'KENNMTL' MNEMONIC,'1010784' PART_NO,'01' PREF_PART_IND union all
  select 'BBBB','001201624777','KENNMTL ','1010784','02'  union all
  select 'BBBB','001201624777','KENNMTL ','1010784','98'  union all
  select 'CCCC','001201624777','KENNMTL ','1010784','98'  union all
  select 'BBBB','001201624777','ATLAS','9753237605','02'  union all
  select 'CCCC','001201624777','ATLAS','9753237605','98'  union all
  select 'BBBB','001201624777','EPIROC','9753250016','01'  union all
  select 'BBBB','001201624777','EPIROC','9753250016','02'  union all
  select 'BBBB','001201624777','KENNMTL','C-1HDSR','01'  union all
  select 'BBBB','001201624777','KENNMTL','C-1HDSR','02'  union all
  select 'BBBB','001201624777','KENNMTL','C-1HDSR','03'  union all
  select 'BBBB','001201624777','KENNMTL','C-1HDSR','98'  union all
  select 'CCCC','001201624777','KENNMTL','C-1HDSR','01'  union all
  select 'BBBB','001201624777','KENNMTL','5TG3','01'  UNION ALL
  select 'CCCC','001201624777','EPIROC','5TG3','01'  UNION ALL
  select 'CCCC','001201624777','WEE','FTG','01' UNION ALL
  select 'CCCC','001201624777','WEE','FTG','02' UNION ALL
  select 'BBBB','001201624777','SONY','HTYR5','98' UNION ALL
  select 'BBBB','001201624777','SONY','HTYR5','99'  
  
  )
  select * into #partmncrank  from q_data;
  
  SELECT  STOCK_CODE,   MNEMONIC,  PART_NO, [BBBB] BTTB_PREF_PART_IND, [CCCC] CCTB_PREF_PART_IND
  FROM (
      SELECT  distinct DSTRCT_CODE, STOCK_CODE, MNEMONIC, PART_NO,  PREF_PART_IND  
      FROM #partmncrank  
  where  DSTRCT_CODE in ('BBBB','CCCC')  
  ) AS SourceTable
  PIVOT (
      min(PREF_PART_IND)
      FOR DSTRCT_CODE IN (BBBB, CCCC)
  ) AS PivotTable 
  ORDER BY MNEMONIC, PART_NO

  The Output I am expecting is