How to pivot a table for a set number of combinations? Ok so I have a table of components and a colleague needs some data from this table in a different format, now I could do it with some python or java code but wondered if there is a simpler way in MS-SQL with a PIVOT maybe?

Components Table:-

ID_RECIPE  COMPONENT    QTY
   1          CPA       1.00
   1          CPC       0.50
   2          CPA       10.0
   2          CPF       7.50
   2          CPH       3.00
   2          CPJ       2.25
   2          CPP       0.25


There is never more than 5 components to a recipe so I want to be able to output something like this:-

ID_RECIPE  COMP_1  COMP_2  COMP_3  COMP_4  COMP_5  QTY_1  QTY_2  QTY_3 QTY_4  QTY_5
   1        CPA     CPC     NULL    NULL    NULL   1.00   0.50   NULL  NULL   NULL
   2        CPA     CPF     CPH     CPJ     CPP    10.00  7.50   3.00  2.25   0.25          


Any ideas would be appreciated.
Using Jonathan Fite's answer from this DBA.SE question and adapting it to your table &amp; data

The CTE ranks the data prior to pivoting twice. You could change the order if you would like to order by something else.

    ;WITH CTE_Rank AS
    (
    SELECT ID_RECIPE,COMPONENT,QTY
        , sComponentName = 'COMP_' + CAST(DENSE_RANK() OVER (PARTITION BY ID_RECIPE ORDER BY COMPONENT) AS VARCHAR(10))
        , sQTY = 'QTY_' + CAST(DENSE_RANK() OVER (PARTITION BY ID_RECIPE ORDER BY COMPONENT) AS VARCHAR(10))
    FROM T
    )


    SELECT ID_RECIPE 
    ,  MAX([COMP_1]) as Comp_1
    ,  MAX([COMP_2]) as Comp_2
    ,  MAX([COMP_3]) as Comp_3
    ,  MAX([COMP_4]) as Comp_4
    ,  MAX([COMP_5]) as Comp_5
    ,  MAX([QTY_1])  as QTY_1
    ,  MAX([QTY_2])  as QTY_2
    ,  MAX([QTY_3])  as QTY_3
    ,  MAX([QTY_4])  as QTY_4
    ,  MAX([QTY_5])  as QTY_5
FROM CTE_Rank AS R
    PIVOT(MAX(COMPONENT) FOR sComponentName IN ([COMP_1], [COMP_2], [COMP_3], [COMP_4], [COMP_5])) AS COMPONENTNAME 
    PIVOT(MAX(QTY) FOR sQTY IN ([QTY_1], [QTY_2], [QTY_3], [QTY_4],[QTY_5])) AS QTYName
GROUP BY ID_RECIPE;


Result

ID_RECIPE   Comp_1  Comp_2  Comp_3  Comp_4  Comp_5  QTY_1   QTY_2   QTY_3   QTY_4   QTY_5
1           CPA      CPC    NULL    NULL    NULL    1.00    0.50    NULL    NULL    NULL
2           CPA      CPF    CPH     CPJ     CPP     10.00   7.50    3.00    2.25    0.25


Table + Data

CREATE TABLE T
(ID_RECIPE int,  COMPONENT char(3),    QTY numeric(10,2));
INSERT INTO T(ID_RECIPE,COMPONENT,QTY)
VALUES
( 1         ,'CPA',       1.00),
( 1         ,'CPC',       0.50),
( 2         ,'CPA',       10.0),
( 2         ,'CPF',       7.50),
( 2         ,'CPH',       3.00),
( 2         ,'CPJ',       2.25),
( 2         ,'CPP',       0.25);


You could remove the warning of the NULL values by checking prior to applying the MAX() (MAX() is there due to the grouping).

DB&lt;>Fiddle