Add null values when pivoting SQL

I have a table with stock prices ordered by dates. I only have those 5 stocks, but I have hundreds of dates. I would like to pivot the stocks, but I have no idea on how to do that.

Data:

   Date   |Stock| Price |
 30/09/22 |  A  | 100.5 |
 30/09/22 |  B  | 151.3 | 
 30/09/22 |  C  | 233.4 | 
 30/09/22 |  D  | 237.2 | 
 30/09/22 |  E  | 38.42 | 
 01/10/22 |  A  | 101.5 |
 01/10/22 |  B  | ----- | 
 01/10/22 |  C  | 237.6 | 
 01/10/22 |  D  | 232.2 | 
 01/10/22 |  E  | 38.54 |

Desired results:

   Date   |  A  |  B  |  C  |  D  |  E  |
 30/09/22 |100.5|151.3|233.4|237.2|38.42|
 01/10/22 |101.5|-----|237.6|232.2|38.54|

CodePudding user response：

Use PIVOT:

SELECT *
FROM   table_name
PIVOT (
  MAX(price) FOR stock IN ('A' AS a, 'B' AS b, 'C' AS c, 'D' AS d, 'E' AS e)
)

Which, for your sample data:

CREATE TABLE table_name ( "DATE", Stock, Price ) AS
  SELECT DATE '2022-09-30', 'A', 100.5  FROM DUAL UNION ALL
  SELECT DATE '2022-09-30', 'B', 151.3  FROM DUAL UNION ALL
  SELECT DATE '2022-09-30', 'C', 233.4  FROM DUAL UNION ALL
  SELECT DATE '2022-09-30', 'D', 237.2  FROM DUAL UNION ALL
  SELECT DATE '2022-09-30', 'E',  38.42 FROM DUAL UNION ALL
  SELECT DATE '2022-10-01', 'A', 101.5  FROM DUAL UNION ALL
  SELECT DATE '2022-10-01', 'B', NULL   FROM DUAL UNION ALL
  SELECT DATE '2022-10-01', 'C', 237.6  FROM DUAL UNION ALL
  SELECT DATE '2022-10-01', 'D', 232.2  FROM DUAL UNION ALL
  SELECT DATE '2022-10-01', 'E',  38.52 FROM DUAL;

Note: DATE is a reserved word and it is bad practice to use it as an identifier. If you must use it as an identifier (don't) then you will need to use a quoted identifier (with the correct case) everywhere it is going to be used.

Outputs:

DATE	A	B	C	D	E
2022-09-30 00:00:00	100.5	151.3	233.4	237.2	38.42
2022-10-01 00:00:00	101.5	null	237.6	232.2	38.52

fiddle

CodePudding user response：

You can use conditional aggregation method to achieve your desired result -

SELECT Date,
       MAX(CASE WHEN Stock = 'A' THEN Price END) AS A,
       MAX(CASE WHEN Stock = 'B' THEN Price END) AS B,
       MAX(CASE WHEN Stock = 'C' THEN Price END) AS C,
       MAX(CASE WHEN Stock = 'D' THEN Price END) AS D,
       MAX(CASE WHEN Stock = 'E' THEN Price END) AS E
  FROM DATA
 GROUP BY Date;

Another Oracle specific answer could be -

SELECT *
  FROM DATA
 PIVOT(MAX(PRICE) FOR Stock IN ('A' AS A, 'B' AS B, 'C' AS C, 'D' AS D, 'E' AS E));

Demo.