AnsweredAssumed Answered

Data from Table A needs to be reorganised and placed into Table B

Question asked by Stu412 on Jun 8, 2015
Latest reply on Jun 9, 2015 by philmodjunk

Title

Data from Table A needs to be reorganised and placed into Table B

Post

It seems odd in a relational DB to have to do what I'm suggesting in the title, so I hope there's no need for this, but needs must.

Table A has sales data in it, organised by customer, type of sale and year of sale, each on a single record because that's how it's presented at import:

CustomerID | SaleType | Year | Value

1010           | Direct      | 2014 | 1000

1010           | Indirect   | 2014 | 800

1010           | Direct      | 2013 | 900

1010           | Indirect    | 2013 |750

1011           | Direct      | 2014 | 1500

1011           | Direct      | 2013 | 750

1011           | Inirect      | 2013 | 500

As you can see, all customers exist on this table and year on year, a sales type may or may not be present, as is the case with customer ID 2011.  This is quite relevant.

I need to get this data from Table A into a format on a list report where I can show the a single row for a sales type with multiple columns for the years and the values within each:

Customer 1010                   2014  |  2013

------------------------------------------------------------

Direct Sales                       1000  |    900

Indirect Sales                      800   |    750

 

Customer 1011

-------------------------------------------------------------

Direct Sales                       1500   |    750

Indirect Sales                     NIL     |    500

If this were Excel, I'd simply pivot it.  It's important to show all the details so that people can see there are no Indirect Sales to Customer 1011 in 2014.

Thanks

 

Outcomes