Oracle check how multiple types corresponding results displayed in a record? (such as A year in the table, type, output, query results showed, when want to type A, output A, type B, the output value B, type C,,,,,)

CodePudding user response:

In details, and see what kind of, your data is and what kind of result,

CodePudding user response:

 CREATE TABLE t (year INTEGER, type VARCHAR2 (20), production INTEGER); 
INSERT INTO t VALUES (2016, 'A', 500); 
INSERT INTO t VALUES (2016, 'B', 600); 
INSERT INTO t VALUES (2016, 'C', 700); 
INSERT INTO t VALUES (2017, 'A', 800); 
INSERT INTO t VALUES (2017, 'B', 900); 

SELECT year, 'type A', pa, type B, pb, 'type C, PC 
The FROM (SELECT year, TYPE, production 
The FROM t 
) 
The PIVOT (SUM (production) FOR (type) IN (' A 'AS pa,' B 'AS pb,' C 'AS PC)) 
The ORDER BY year. 

YEAR | | PA 'type A' | 'type B | | in PB' type C '| | PC 
-- - | -- - | -- - | -- - | -- - | -- -- -- -- -- - | -- - | 
2016 A | 500 | | type type B C | 700 | | 600 | type 
2017 | type A | 800 | | 900 | B type C | |