Dynamic multiple PIVOT on numeric column

Question

I have a table having data like

And I want to pivot category(which can increase/decrease) column with period column and also want sold_amt, purchase_price, gross_profit as row, please see required image like

Thanks in advance.

Please find create and insert statements of

CREATE TABLE temp_key_category (
    category_code varchar(30),
    sold_amt DECIMAL(12,4), 
    purchase_price DECIMAL(12,4), 
    gross_profit DECIMAL(12,4),
    item_qty DECIMAL(12,2),  
    period VARCHAR(100),
    salesperson_code VARCHAR(100),
    salesperson_name VARCHAR(100)
);

insert-

INSERT INTO temp_key_category values('BICEGO',  17433.0000, 16740.0000, 3.9752, 8.00,   'Rolling 12 Periods',   166,    'Ben Ehrmann')
INSERT INTO temp_key_category values('BRIDAL',  1533.0000,  1680.0000,  3.0,    5.00,   'Rolling 12 Periods',   116,    'Anthony')
INSERT INTO temp_key_category values('BRIDAL',  5533.0000,  1590.0000,  3.5,    5.00,   'Current Period',   116,    'Anthony')
INSERT INTO temp_key_category values('LOOSE DIAMONDS',  69131.0000, 39117.4000, 43.4155,    5.00,   'Current Period',   116,    'Anthony')  
INSERT INTO temp_key_category values('LOOSE DIAMONDS',  8131.0000,  3517.4000,  43.458, 5.00,   'Rolling 12 Periods',   116,    'Anthony')  
INSERT INTO temp_key_category values('YURMAN',  7131.0000,  3517.4000,  43.458, 5.00,   'Rolling 12 Periods',   116,    'Kiley')    


select * from temp_key_category

instead of an image please provide table structure and insert statement of sample data to recreate the table. — Kazi Mohammad Ali Nur Romel, Feb 25 '21 at 08:34

score 1 · Accepted Answer · answered Feb 25 '21 at 09:53

Here goes your dynamic pivot:

 DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)
    
SET @cols = STUFF((SELECT distinct ',' + quotename(concat(category_code,'_',period))
            FROM temp_key_category 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
               
            
       
set @query = 'select salesperson_code, ' + @cols + ' from 
            (
  select c.salesperson_code  ,
  SalesOrPurchase,concat(category_code,''_'',period)cetegoryperiod
from temp_key_category
cross apply
(
  select concat(salesperson_code,''_'',''sold_amt''), sold_amt union all
  select concat(salesperson_code,''_'',''purchase_price''), purchase_price 
  
) c (salesperson_code , SalesOrPurchase)

) x
            pivot 
            (
              sum(salesorpurchase)
  for cetegoryperiod in (' + @cols + ')

            ) p 
            group by salesperson_code ,' + @cols 


execute(@query);

Output:

Dynamic multiple PIVOT on numeric column

1 Answers1