I have a request from a user to display data from two tables in a one to many relationship in one row. The data looks like this:
a customer can have multiple shares.
the users wants the information to be displayed in the following way:
CustomerID | Name | Share1 Currency | Share1 Amount | Share2 Currency |Share2 Amount |
1 |Tom |USD |12 |GBP |10 |
they want the records to be displayed on one row with columns for the shares to be dynamically added for each record if there are shares. is this possible in SQL and if so can you point me in the right direction please? it looks like a pivot tables may work but everything I've read so far is dealing with aggregates.