How do a pivot on sql server on one column but renamed dynamics column

I’ve a table of prices With Reference, Price Category (Ex professional/Customer…) , the weight and the price Each article have X lines for same Reference, Price Category depend of weight I Want a return with Reference, Price Category, Price1,Price2… I try to adapt codes I’ve found but I’ve a problem to linked weight and named columns PRIX1,PRIX2…

declare @MAxcols as int  
DECLARE @cols AS NVARCHAR(MAX)='';
/*max of differents price=max columns dynamic to add*/
set @Maxcols = (select  max(NBLignes) from (select  count(*) as NBLignes
FROM [Z_TARIFS_VENTE] as Results 
GROUP BY AR_REF,Cat_Prx) as tmp)

/*Build all dynamic columns */
DECLARE @cnt INT = 0;
WHILE @cnt < @Maxcols
BEGIN
  SET @cols = @cols  + 'PRIX' + cast(@cnt+1 as nvarchar(3)) + ','
  SET @cnt = @cnt + 1;
END
SET @cols=LEFT(@cols, LEN(@cols) - 1)/*remove last , = 'PRIX1,PRIX2,PRIX3...PRIXX'*/
DECLARE @query AS NVARCHAR(MAX);
SELECT @query =
'SELECT
[AR_Ref],
[Cat_Prx],
' + @cols + '
FROM (
Select
  [AR_Ref],
     [Cat_Prx],
     [weight] ,
     [PRIX] 
From
    [Z_TARIFS_VENTE]
) t
PIVOT 
(
Sum(PRIX)
FOR weight IN( ' + @cols + ' )' +/* Here my problem*/
' ) AS p; ';
Execute(@query);

I don’t want have each weight in separate column but only classify on Prix1, prix2…

Answer

Finally… Force to try I create a view on my table With row number based on my group by

 ROW_NUMBER() OVER (PARTITION BY [AR_Ref], [Cat_Prx]...)

AND In a stored procedure

declare @MAxcols as int  
set @Maxcols = (select  max(NBLignes) from (select  count(*) as NBLignes
FROM [Z_TARIFS_VENTE] as Results 
GROUP BY AR_REF,EG_Enumere,TQ_RefCF) as tmp)
DECLARE @query AS NVARCHAR(MAX);
SET @query ='SELECT [AR_Ref], [EG_Enumere],[TQ_RefCF]'
DECLARE @cnt INT = 1;
WHILE @cnt < @Maxcols + 1
BEGIN
 SET @query = @query  + ',CASE WHEN IdxRow=' + CAST(@cnt as nvarchar(3)) + ' THEN PRIX ELSE NULL END AS PRIX' + CAST(@cnt as nvarchar(3))
SET @cnt = @cnt + 1;
END
SET @query = @query  + ' FROM Z_TARIFS_VENTE_ROWNUM'
EXECUTE sp_executesql @query

Now I just need to group by and on each added column put Max(Prix1) as prix1 ,…always using a variable string