My issue is how to show related data per row? Let me explain in an example. For this input:
Shipment:
LOAD * INLINE [
ShipmentID, Qty
1, 10
2, 30
3, 20
];
Event:
LOAD * INLINE [
EventID, ShipmentID, EventDate, EventName
1, 1, '2015-08-01', 'Processed'
2, 2, '2015-09-05', 'Shipped'
3, 3, '2015-10-01', 'Processed'
4, 1, '2015-08-04', 'Shipped'
5, 2, '2015-09-01', 'Processed'
6, 2, '2015-09-09', 'Delivered'
7, 3, '2015-10-03', 'Transit'
8, 3, '2015-10-02', 'Shipped'
9, 3, '2015-10-15', 'Wait'
10, 1, '2015-08-15', 'Delivered'
];
I would like to get this as output (Ev. short for Event):
ShipmentID
Ev.ID_1
Ev.Date_1
Desc_1
Ev.ID_2
Ev.Date_2
Desc_2
Ev.ID_3
Ev.Date_3
Desc_3
Ev.ID_4
Ev.Date_4
Desc_4
1
1
2015-08-01
Processed
4
2015-08-04
Shipped
10
2015-08-15
Delivered
2
5
2015-09-01
Processed
2
2015-09-05
Shipped
6
2015-09-09
Delivered
3
3
2015-10-01
Processed
8
2015-10-02
Shipped
7
2015-10-04
Transit
9
2015-10-15
Wait
I know that I can't have dynamic number of column. Let's say that it's hardcoded to max 4. I will change as needed.
Order in which events should be presented is by date only. ID or Description can't be used for sorting since events are stored in sort of random order, and ID is assigned as they arrive. Also, Description can be more or less any string, but for convenience I used only 5 different.
I would like to have this in pivot table, but it's not that important where it's presented as long as I get it done.
I was on vacation and I didn't have time to check your solutions in detail yet. Just wanted to say thank you again, and I will close this question as soon as I confirm that I solved my case. Your help is greatly appreciated!
