Pivot but preserve all rows on Aggregate column

Question

Post reply

Pivot but preserve all rows on Aggregate column

getsaby

Old Hand

Points: 346

December 10, 2025 at 5:19 pm

Go to Answer

#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

Screenshot 2025-12-10 110131

ScottPletcher

SSC Guru

Points: 101223

December 10, 2025 at 7:58 pm

Answer

#4698543

I'm not sure PIVOT can give you the results you want.

Maybe try this instead?:

SELECT 
    COALESCE(BBBB.STOCK_CODE, CCCC.STOCK_CODE) AS STOCK_CODE,
    COALESCE(BBBB.MNEMONIC, CCCC.MNEMONIC) AS MNEMONIC,
    COALESCE(BBBB.PART_NO, CCCC.PART_NO) AS PART_NO,
    COALESCE(BBBB.PREF_PART_IND, '') AS BTTB_PREF_PART_IND,
    COALESCE(CCCC.PREF_PART_IND, '') AS CCTB_PREF_PART_IND
FROM (
    SELECT STOCK_CODE, MNEMONIC, PART_NO, PREF_PART_IND, 
        ROW_NUMBER() OVER(PARTITION BY STOCK_CODE, MNEMONIC, PART_NO ORDER BY PREF_PART_IND) AS row_num
    FROM #partmncrank
    WHERE DSTRCT_CODE = 'BBBB'
) AS BBBB
FULL OUTER JOIN (
    SELECT STOCK_CODE, MNEMONIC, PART_NO, PREF_PART_IND, 
        ROW_NUMBER() OVER(PARTITION BY STOCK_CODE, MNEMONIC, PART_NO ORDER BY PREF_PART_IND) AS row_num
    FROM #partmncrank
    WHERE DSTRCT_CODE = 'CCCC'
) AS CCCC ON CCCC.STOCK_CODE = BBBB.STOCK_CODE AND CCCC.MNEMONIC = BBBB.MNEMONIC AND CCCC.PART_NO = BBBB.PART_NO AND
    CCCC.row_num = BBBB.row_num
ORDER BY 1, 2, 3, 4, 5

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

getsaby Old Hand Points: 346 More actions · Answer 1

Thank you, @ScottPletcher. The SQL query produced the output exactly as I needed.

ScottPletcher SSC Guru Points: 101223 More actions · Answer 2

Great, glad it helped. Many thanks for the feedback.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101223 More actions · Answer 3

You can also do it with a single result set. Which performs better would depend, so you'd have to test that out.

This method would also be much easier if you have more DSTRCT_CODEs, particularly more than 3.

SELECT 
    STOCK_CODE, MNEMONIC, PART_NO,
    MAX(CASE WHEN DSTRCT_CODE = 'BBBB' THEN PREF_PART_IND ELSE '' END) AS BTTB_PREF_PART_IND,
    MAX(CASE WHEN DSTRCT_CODE = 'CCCC' THEN PREF_PART_IND ELSE '' END) AS CCTB_PREF_PART_IND
FROM (
    SELECT DSTRCT_CODE, STOCK_CODE, MNEMONIC, PART_NO, PREF_PART_IND, 
        ROW_NUMBER() OVER(PARTITION BY DSTRCT_CODE, STOCK_CODE, MNEMONIC, PART_NO ORDER BY PREF_PART_IND) AS row_num
    FROM #partmncrank
    WHERE DSTRCT_CODE IN ('BBBB', 'CCCC')
    --ORDER BY 1, 2, 3, 4, 5, 6
) AS data
GROUP BY 
    STOCK_CODE, MNEMONIC, PART_NO, row_num
ORDER BY 1, 2, 3, 4, 5

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".