How to pivot a table for a set number of combinations? [duplicate]

Question

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.

Answer 1

Using Jonathan Fite's answer from this DBA.SE question and adapting it to your table & 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<>Fiddle

Answer 2

Try this one...

SELECT ID_RECIPE,
        MAX(CASE WHEN rownumber = 1 THEN component END) AS COMP_1,
        MAX(CASE WHEN rownumber = 2 THEN component END) AS COMP_2,
        MAX(CASE WHEN rownumber = 3 THEN component END) AS COMP_3,
        MAX(CASE WHEN rownumber = 4 THEN component END) AS COMP_4,
        MAX(CASE WHEN rownumber = 5 THEN component END) AS COMP_5,
        MAX(CASE WHEN rownumber = 1 THEN qty END) AS QTY_1,
        MAX(CASE WHEN rownumber = 2 THEN qty END) AS QTY_2,
        MAX(CASE WHEN rownumber = 3 THEN qty END) AS QTY_3,
        MAX(CASE WHEN rownumber = 4 THEN qty END) AS QTY_4,
        MAX(CASE WHEN rownumber = 5 THEN qty END) AS QTY_5
FROM   (SELECT *, Row_number() OVER(partition BY ID_RECIPE ORDER BY ID_RECIPE) AS RowNumber
        FROM   tablename) t1
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.0 |  7.50 | 3.00  | 2.25  | 0.25  |
+-----------+--------+--------+--------+--------+--------+-------+-------+-------+-------+-------+