We help IT Professionals succeed at work.
Get Started

Merge rows into columns using TSQL

noel58
noel58 asked
on
3,879 Views
Last Modified: 2012-08-13
I have a database with a many to many relationship between Funds and Fund Managers, for a specific report I need to get all the funds in the database and their fund managers, but only 1 row for each fund, and multiple sets of columns for each fund manager, so a simple join wont work.

Bare in mind that there could be any number of Fund Managers related to a Fund and that I need to return all the Funds, How would I go about merging all the rows for each Fund into a single row each and add the Dynamic columns for each Fund Manager?

Many Thanks
//Here is the TSQL im using for the notmal join...
 
SELECT     Fund.FundID, Fund.FundDisplayName, FundManager.FundManagerID, FundManager.FundManagerDisplayName
FROM         Fund INNER JOIN
                      FundFundManager ON Fund.FundID = FundFundManager.FundFundManagerFundID INNER JOIN
                      FundManager ON FundFundManager.FundFundManagerFundManagerID = FundManager.FundManagerID
WHERE     (Fund.FundID = 2)
 
//and here is the table it returns...
 
FundID	FundDisplayName	FundManagerID	FundManagerDisplayName
2	Test Fund	3		djkhrejr
2	Test Fund	1		test fund manager
2	Test Fund	2		jhsdfjhmgjhg
 
//However for this particular report I need the data to be in this layout...
 
FundID	FundDisplayName	FundManagerID1	FundManagerDisplayName1	FundManagerID2	FundManagerDisplayName2	FundManagerID3	FundManagerDisplayName3
2	Test Fund	3		djkhrejr		1		test fund manager	2		jhsdfjhmgjhg

Open in new window

Comment
Watch Question
Top Expert 2007
Commented:
This problem has been solved!
Unlock 1 Answer and 9 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE