CODEWITHSUNDEEP
sqloracle-databasepivot
RNJ
RNJ

Reputation: 15562

Pivoting SQL into multiple columns

I think I have to use pivot here but am not too sure.

I have SQL for the following table

|Customer|Day|Amt|
|  1     | 1 | x1 |
|  2     | 1 | x2 |
|  3     | 1 | x3 |
|  1     | 2 | x4 |
|  3     | 3 | x5 |
|  3     | 5 | x6 |

I want to pivot it so that it looks like

|Customer | 1   |  2 |  3 | 4  | 5  |
|  1      | x1  | x4 |    |    |    |  
|  2      | x2  |    |    |    |    |  
|  3      | x3  |    | x5 |    | x6 |

with the blanks cells just being null.

In the end I want to sum all the values in day 1,2 and 3 which may affect how this is designed.

Has anyone got any tips on how I can transpose this data. The number of customers and days can be vary which adds to the complications?

Any pointers would be appreciated

Thanks

Upvotes: 0

Views: 161

Answers (3)

David Adlington
David Adlington

Reputation: 666

SQL Fiddle example for MS-SQL

http://sqlfiddle.com/#!3/8c0a0/1

Upvotes: -1

Yván Ecarri
Yván Ecarri

Reputation: 1738

If you only want to sum by customer and day, just group by:

SELECT SUM(Amt) Amt, Customer, Day
FROM your_table
GROUP BY Customer, Day

If you need to display results as columns, you can use PIVOT clause (assuming you have Oracle 11g or higher)

SELECT * FROM 
(
   SELECT customer, DAY, amt 
   FROM your_table
)
pivot (SUM(amt) FOR (DAY) IN(1, 2, 3, 4, 5));

Upvotes: 0

ajmalmhd04
ajmalmhd04

Reputation: 2602

SELECT * FROM 
(
SELECT customer, DAY, amt 
FROM your_table
)
pivot (SUM(amt) FOR ( DAY) IN(1, 2, 3, 4, 5));

Upvotes: 2

Related Questions