I know there is a way to do this, but not sure how? I have a table (or multple tables) which contain records of related items (unlimited in the table). However, I need to return a single record format for extracting to CSV. I would like to do this in SQL query (currently using MS SQL Server 2005 Express Edition).
The real DB and Tables contain much more data however, if I can get this information out I should be able to include say, Address, Phone, BirthDate... with the additional columns or concatenate into the five,
An example (probably not the best) would be two tables (note the pipe is the separator for display only)
[Person] - Sample Data
1 | Al Smith
2 | Bob Jones
[Friend] - Sample Data
1 | 1 | Stan Lee
2 | 1 | Mike Roberts
3 | 1 | Phil Paterson
4 | 2 | Mike Reilly
5 | 2 | Harry Johnson
I would like to return say five(max) fixed column (whether there are more or less friends)
Al Smith | Stan Lee | Mike Robert | Phil Paterson | BLANK | BLANK
Bob Jones | Mike Reilly | Harry Johnson | BLANK | BLANK | BLANK
Also, if there is a better way to do this I am open for options!