Pivot mixed data-CodePudding

I have the below table:

 PART    ATTR    VAL
-------------------
   A    ATTR1   VAL1
   A    ATTR2   VAL2
   A    ATTR3   VAL3
   A    ATTR4   VAL4
   A    ATTR5   VAL5
   B    ATTR1   VAL6
   B    ATTR2   VAL7
   B    ATTR3   VAL8
   B    ATTR4   VAL9
   B    ATTR5   VAL10

And I would like to obtain the following output:

PART    ATTR1   DETAIL  DETAIL2
-------------------------------
   A     VAL1     VAL2     VAL4
   B     VAL6     VAL7     VAL9

The logic being: For each 'PART', pivot and put:

the value for 'ATTR1' in a column named "ATTR1".
from those entries whose 'ATTR' is either 'ATTR2' or 'ATTR3', put the minimum VAL in column called 'DETAIL'.
from those entries whose 'ATTR' is either 'ATTR4' or 'ATTR5', put the minimum VAL in column called 'DETAIL2'.

CodePudding user response：

By using a CASE statement and grouping in the base of your pivot, you can then use those grouped values to PIVOT your results. If you are truly looking for the MIN to be displayed in the results, your example output will not match your sample data since VAL10 will come before VAL9 when using MIN or sorting any string.

Query

WITH
    parts (part, attr, val)
    AS
        (SELECT 'A', 'ATTR1', 'VAL1' FROM DUAL
         UNION ALL
         SELECT 'A', 'ATTR2', 'VAL2' FROM DUAL
         UNION ALL
         SELECT 'A', 'ATTR3', 'VAL3' FROM DUAL
         UNION ALL
         SELECT 'A', 'ATTR4', 'VAL4' FROM DUAL
         UNION ALL
         SELECT 'A', 'ATTR5', 'VAL5' FROM DUAL
         UNION ALL
         SELECT 'B', 'ATTR1', 'VAL6' FROM DUAL
         UNION ALL
         SELECT 'B', 'ATTR2', 'VAL7' FROM DUAL
         UNION ALL
         SELECT 'B', 'ATTR3', 'VAL8' FROM DUAL
         UNION ALL
         SELECT 'B', 'ATTR4', 'VAL9' FROM DUAL
         UNION ALL
         SELECT 'B', 'ATTR5', 'VAL10' FROM DUAL)
  SELECT *
    FROM (  SELECT part,
                   CASE
                       WHEN attr IN ('ATTR1') THEN 'ATTR1'
                       WHEN attr IN ('ATTR2', 'ATTR3') THEN 'DETAIL'
                       WHEN attr IN ('ATTR4', 'ATTR5') THEN 'DETAIL2'
                   END          AS attr_name,
                   MIN (val)    AS val
              FROM parts
          GROUP BY part,
                   CASE
                       WHEN attr IN ('ATTR1') THEN 'ATTR1'
                       WHEN attr IN ('ATTR2', 'ATTR3') THEN 'DETAIL'
                       WHEN attr IN ('ATTR4', 'ATTR5') THEN 'DETAIL2'
                   END)
         PIVOT (MIN (val)
               FOR attr_name
               IN ('ATTR1' AS attr1, 'DETAIL' AS detal, 'DETAIL2' AS detail2))
ORDER BY part;

Result

   PART    ATTR1    DETAL    DETAIL2
_______ ________ ________ __________
A       VAL1     VAL2     VAL4
B       VAL6     VAL7     VAL10