0

With my SQL query I get the following output:

My Output

ITEM_ID USP_NO Value

2616761 1      Type Bijtafel
2616761 2      Materiaal Steen
2616761 3   
2616761 4   
2616761 5   
5037554 1      Materiaal Geïmpregneerd hout
5037554 2   
5037554 3

But I want to convert my output to have a single ITEM_ID with in different columns the values like the following

ITEM_ID USP1                          USP2
2616761 Type Bijtafel                 Materiaal Steen
5037554 Materiaal Geïmpregneerd hout

My Query is the following:

SELECT * 
FROM PIM_USP
where item_ID IN (SELECT Item_ID 
                  from PIM_ITEM where 
                  PIM_ITEM.brick_ID=10002084)
Joerian Droog
  • 9
  • 3
  • 1
    Which [DBMS product](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... –  May 30 '18 at 10:04
  • Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) – Jodrell May 30 '18 at 10:14
  • Consider handling issues of data display in the presentation layer/application-level code, assuming you have that (e.g. a simple PHP loop acting upon an ordered array). – Strawberry May 30 '18 at 10:16

1 Answers1

1

You want conditional aggregation :

SELECT  ITEM_ID,
        MAX(CASE WHEN USP_NO = 1 THEN Value END) AS USP1,
        MAX(CASE WHEN USP_NO = 2 THEN Value END) AS USP2,
        MAX(CASE WHEN USP_NO = 3 THEN Value END) AS USP3,
        MAX(CASE WHEN USP_NO = 4 THEN Value END) AS USP4,
        MAX(CASE WHEN USP_NO = 5 THEN Value END) AS USP5
FROM PIM_USP p
GROUP BY ITEM_ID;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52