Creating a result that will show one row of data for a 2 table join that has one record in 1st table 1

[jchewsmith]

Is there a way to create a join that will join 2 tables, the first table has a record of a acct # and name, the 2nd table has multiple rows by account # and I want them to show the result as one row with new columns for each row field data.

So table Customer has Customer ID and Name
Table orders as customer ID, order 1 with a red ball, order 2 with a blue ball, order 3 with a green ball, etc.

I want results as follows:
Customer ID Customer Name Order 1 order 2 order 3
123 Joe Customer red ball blue ball green ball

 

[gmmastros]

You can do this with SQL Server (research pivot table), but I would recommend that you do this in your front end application instead. I suggest it this way because the number of orders can vary, right? You could have a customer with 0 orders, 1 order, 3 orders, or 300 orders. How many columns should there be in the final output?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jchewsmith]

Yes there can be a variety of different results depending on the customer. 0 to 50.

I appreciate the advice and will try your suggestion of handling it with the front end app.

Thanks