Could you please help me with my problem? I am using xampp server with mysql database.
I have a table that looks like this:
|----|------------|--------------|-------------|------------|----------|
| id | period | category | subcategory | amount | currency |
|----|------------|--------------|-------------|------------|----------|
| 1 | 2014-01-01 | Category One | SubCat One | 325.48 | EUR |
| 2 | 2014-01-01 | Category One | SubCat One | 1680.20 | GBP |
| 3 | 2014-01-01 | Category One | SubCat Two | 229.78 | EUR |
| 4 | 2014-01-01 | Category One | SubCat Two | 1152.41 | GBP |
| 5 | 2014-01-01 | Category Two | SubCat One | 417.92 | EUR |
| 6 | 2014-01-01 | Category Two | SubCat One | 980.64 | GBP |
|----|------------|--------------|-------------|------------|----------|
I need to get the result that items will be grouped by three fields (period, category, subcategory) and dynamic columns with each currency will be added to each group. Something like this:
|------------|--------------|-------------|--------|---------|
| period | category | subcategory | EUR | GBP |
|------------|--------------|-------------|--------|---------|
| 2014-01-01 | Category One | SubCat One | 325.48 | 1680.20 |
| 2014-01-01 | Category One | SubCat Two | 229.78 | 1152.41 |
| 2014-01-01 | Category Two | SubCat One | 417.92 | 980.64 |
|------------|--------------|-------------|--------|---------|
What sql query should I run against the table to get the desired result?
The trick is that the query should automatically manage many currencies, so when I add new records with new currency, another column will be dynamically created in my result recordset.
Table after adding rows 7 and 8 with new currency (USD).
|----|------------|--------------|-------------|------------|----------|
| id | period | category | subcategory | amount | currency |
|----|------------|--------------|-------------|------------|----------|
| 1 | 2014-01-01 | Category One | SubCat One | 325.48 | EUR |
| 2 | 2014-01-01 | Category One | SubCat One | 1680.20 | GBP |
| 3 | 2014-01-01 | Category One | SubCat Two | 229.78 | EUR |
| 4 | 2014-01-01 | Category One | SubCat Two | 1152.41 | GBP |
| 5 | 2014-01-01 | Category Two | SubCat One | 417.92 | EUR |
| 6 | 2014-01-01 | Category Two | SubCat One | 980.64 | GBP |
|----|------------|--------------|-------------|------------|----------|
| 7 | 2014-01-01 | Category One | SubCat One | 2525.50 | USD |
| 8 | 2014-01-02 | Category One | SubCat One | 700.12 | USD |
|----|------------|--------------|-------------|------------|----------|
Result (another currency column created dynamically).
|------------|--------------|-------------|--------|---------|---------|
| period | category | subcategory | EUR | GBP | USD |
|------------|--------------|-------------|--------|---------|---------|
| 2014-01-01 | Category One | SubCat One | 325.48 | 1680.20 | 2525.50 |
| 2014-01-01 | Category One | SubCat Two | 229.78 | 1152.41 | 0 |
| 2014-01-01 | Category Two | SubCat One | 417.92 | 980.64 | 0 |
| 2014-01-02 | Category One | SubCat One | 0 | 0 | 700.12 |
|------------|--------------|-------------|--------|---------|---------|
Is there any simple way I can achieve this using sql query? Or would that required some php programming to prepare the results in desired way?
I appreciate your help,
Thanks