I have a table that saves the customers details:
customer
cid| name | email | and so on..
1 annu ....
2 rita ....
3 tina ....
Event table saves the different events:
eid | name
1 abc
2 xyz
3 pqr
Now one customer can be interested in more than one event. event_assigned table is as follows:
eid |cid
1 1
2 1
3 2
2 3
And next is the catalog table that has all the types of catalogs.
cat_id | cat_name
1 food
2 clothing
3 accessories..
Now catalogs are sent to each customers as per the event that they are interested in and the details of the sent catalogs are maintained in cat_sent table as follows:
cid | cat_id | date | eid | edition
1 1 2/2/2015 1 v1
1 2 3/2/2015 2 v1
2 3 3/2/2015 3 v1
1 2 4/2/2015 1 v2
2 2 4/2/2015 3 v1
1 1 5/2/2015 1 v2
Now the catalogs are sent to the customers of the respective event.As you can see one catalog can be sent to same customer with same event but of different editions. Now what i want is to get the output as follows by joining all the above tables.
cid | eid | food | clothing | accessories etc..
1 1 **2/2/2015-v1/5/2/2015-v2** 4/2/2015-v2 null
1 2 null 3/2/2015-v1 null
2 3 null 4/2/2015-v1 3/2/2015-v1
The catalog names should be the dynamic columns with the date and edition concatenated as their value with seperator '-' grouped by customer ids and event ids. If customer with same event have more than one same catalogs than it should be grouped and concatenated with a seperator '/' eg. is shown above(cid->1 with eid-> 1 and two same cat_id).
If the data for any catalog is missing than it should be shown as null.
