QV12 SR 4
Hoping someone can point me in the right direction.
I need to concatenate some answers into a single row whereby some fields will be pipe delimited but i need to maintain a certain order in the pipe delimited fields.
Best explained with an example i think. Let's say i have this data. NB Customer # is always unique per user all others may not be
| UserID | BPN | Customer # | Sub Date |
|---|
| 123 | 100888 | 400123 | 11/07/2013 |
| 123 | 100599 | 400456 | 08/07/2014 |
| 123 | 100637 | 400789 | 11/07/2013 |
| 123 | 100552 | 400321 | 22/06/2016 |
| 123 | 100552 | 400654 | 08/07/2014 |
| 123 | 100792 | 400987 | 27/10/2016 |
| 123 | 100599 | 400159 | 03/06/2015 |
So i need to end up with one row per user - so the BPN, Customer # and Sub Date fields would be pipe delimited as follows
| UserID | BPN | Customer # | Sub Date |
|---|
| 123 | 100888|100599|100637|100552|100552|100792|100599 | 400123|400456|400789|400321|400654|400987|400159 | 11/07/2013|08/07/2014|11/07/2013|22/06/2016|08/07/2014|27/10/2016|03/06/2015 |
So in the above result the first pipe in each field corresponds to the first row from the top table and the second pipe corresponds to the second row and so on for however many rows per user.
It doesn't matter by which column(s) the top table is initially ordered.
Thought i might be able to use Concat with a weighted sort but haven't managed to crack it yet and may be over thinking the issue!?
Hope that makes sense - any ideas greatly welcomed
