sql pivot query

Experts:

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:

Table Customer
CustomerID
Name

Table Shares
ShareID
Currency
Class
Amount

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.

Thanks,
telliot79Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Could you please give some sample data in Customer and Shares tables so we can test the solution before posting it?

re: a customer can have multiple shares. \So, CustomerID as FK needs to be in Shares table as well. If not, how these tables are linked?

Mike

I am starting with:
CustomerID              Name              
1                                  Tom
2                                  John

for Customer table, and for Shares:

CustomerID              ShareID            Currency               Class            Amount  
1                                    1                      USD                            tbd               12.00
1                                    2                      GBP                            tbd               10.00
2                                    3                      USD                            tbd                22.00
2                                    4                      GBP                            tbd                30.00
0
PortletPaulfreelancerCommented:
Is there a limit to the number of columns?
0
Mike EghtebasDatabase and Application DeveloperCommented:
Hi Paul,

It seems this is sort of money exchange (before/after data) meaning possibly it is just Share1 and Share2. Others likewise per customer.

if its of any help:
create table #Customer(CustomerID int, Name varchar(10));
create table #Shares(CustomerID int, ShareID int, Currency varchar(10), Class varchar(10), Amount decimal(10,2));

Insert Into #Customer(CustomerID, Name) Values
(1, 'Tom')
, (2, 'John');

Insert Into #Shares(CustomerID, ShareID, Currency, Class, Amount) Values
(1, 1, 'USD', 'tbd', 12)
,(1, 2, 'GBP', 'tbd', 10)
,(2, 3, 'USD', 'tbd', 22)
,(2, 4, 'GBP', 'tbd', 30);

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

telliot79Author Commented:
eghtebas,

your table structure and sample data is correct.

Paul,

There is no limit to the number of columns.
0
PortletPaulfreelancerCommented:
>>"There is no limit to the number of columns."
As I thought & thus making the requirement more difficult to satisfy

I mean this with good intent and given with my most earnest gaze: this style of data transformation isn't a good fit to SQL and I would advise you do it in the "presentation layer" instead of SQL if it is available to you, e.g. PHP/HTML

However what you ask for can be achieved using "dynamc SQL" but it isn't that simple. Here is some relevant references:
Dynamic Pivot Procedure for SQL Server (Mark Willis)
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28188971.html#a39338027
http://www.experts-exchange.com/questions/28188971/Need-some-help-using-pivot-in-a-query-SQL-2008-R2.html
http://www.experts-exchange.com/questions/28698875/Can-you-please-help-in-below-sql-server-query.html#a40894549
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
telliot79Author Commented:
Thanks for all your help. The information you provided has helped me decide to take a different approach.

for those who are interested.

-I've basically created a table variable
-I've added the shares for each customer giving them a row number.
-I've then joined to the table variable four times. (this differs from my original approach.)
-I've then added a record count of each customer's share records to the report so the users will know if there's more than four they can use the application to find this information.

not ideal, but will do the job.

thanks for your assistance.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.