 Q           The title is as follows ：

There is a table in the database as shown below , Table, sales.

 year quarter Sales volume 1991 1 11 1991 2 12 1991 3 13 1991 4 14 1992 1 21 1992 2 22 1992 3 23 1992 4 24

requirement ： Write a SQL Statement to query the result as shown below .

 year First quarter The two quarter third quater In the fourth quarter 1991 11 12 13 14 1992 21 22 23 24

A           The answer is as follows ：

The environment is as follows ：

 ```                                      ``` CREATE TABLE sales( year NUMBER, quarter NUMBER , Sales volume NUMBER); INSERT INTO sales VALUES(1991, 1 ,11); INSERT INTO sales VALUES(1991, 2 ,12); INSERT INTO sales VALUES(1991, 3 ,13); INSERT INTO sales VALUES(1991, 4 ,14); INSERT INTO sales VALUES(1992, 1 ,21); INSERT INTO sales VALUES(1992, 2 ,22); INSERT INTO sales VALUES(1992, 3 ,23); INSERT INTO sales VALUES(1992, 4 ,24); SELECT * FROM sales; ————————————     year quarter Sales volume 1 1991 1 11 2 1991 2 12 3 1991 3 13 4 1991 4 14 5 1992 1 21 6 1992 2 22 7 1992 3 23 8 1992 4 24

This is a row column problem , Using aggregate functions +DECODE or CASE Just answer , As shown below

 ```                                        SELECT  year ,  SUM(CASE WHEN  quarter =1 THEN  Sales volume  ELSE 0 END) AS  First quarter , SUM(CASE WHEN  quarter =2 THEN  Sales volume  ELSE 0 END) AS  The two quarter , SUM(CASE WHEN  quarter =3 THEN  Sales volume  ELSE 0 END) AS  third quater , SUM(CASE WHEN  quarter =4 THEN  Sales volume  ELSE 0 END) AS  In the fourth quarter FROM SALES GROUP BY  year ;```

If you use this question PIVOT function , As shown below ：

 ```                                        SELECT *  FROM SALESPIVOT(SUM( Sales volume )   FOR  quarter  IN(1 AS " First quarter ", 2 AS " The two quarter ", 3 AS " third quater ", 4 AS " In the fourth quarter ")) ORDER BY 1;```

If you use a temporary table , As shown below ：

 ```                                        SELECT T. year ,       NVL(SUM(T1. First quarter ),0) AS " First quarter ",       NVL(SUM(T2. The two quarter ),0) AS " The two quarter ",       NVL(SUM(T3. third quater ),0) AS " third quater ",       NVL(SUM(T4. In the fourth quarter ),0) AS " In the fourth quarter "  FROM (SELECT  year , Sales volume  AS " First quarter " FROM SALES A WHERE A. quarter  = '1') T1,       (SELECT  year , Sales volume  AS " The two quarter " FROM SALES A WHERE A. quarter  = '2') T2,        (SELECT  year , Sales volume  AS " third quater " FROM SALES A WHERE A. quarter  = '3') T3,        (SELECT  year , Sales volume  AS " In the fourth quarter " FROM SALES A WHERE A. quarter  = '4') T4,       (SELECT DISTINCT  year  FROM SALES) T WHERE T. year  = T1. year (+)   AND T. year  = T2. year (+)   AND T. year  = T3. year (+)   AND T. year  = T4. year (+) GROUP BY T. year  ORDER BY 1;```