What kind of SQL join do I need to compress a One to Many relationship into the same view row?

Edit: this isn't to be a dynamic output, the output view structure is fixed.

I am trying to create a SQL Server view that shows a single fixed column row for each user, and flattens out an associated one to many table into that row.

Although the associated table has a one to many relationship, the output table structure is limited to 4 elememts form that table.

My table structure is like so:

User (Id, FirstName, LastName)
Assessment (Id, Date, Location, User_Id)
Topics (Id, Topic, Assessment_Id)

Where the Assessment is joined to the User by the User_Id (One 2 One), and the Topics are joined to the Assessment by the Assessment_Id.

So, if I have three topics for an assessment, I'd want the view to look something like:

User_Id | FirstName | LastName | Date   | Location | Topic1 | Topic2  | Topic3 | Topic4 |
    1   |  dave     |  toby    | 2/2/11 | In situ  | apples | pears   | lemons | NULL   |

My current SQL looks like this:

SELECT  User.Id, User.FirstName, User.L开发者_如何学CastName, Assessment.Date, Assessment.Location, Topic.Topic
FROM   User LEFT OUTER JOIN
           Assessment INNER JOIN
              Topic ON Assessment.Id = Topic.Assessment_Id ON 
                  User.Id = Assessment.User_Id

But this returns a row for each concern - it doesn't compress them to one line. I've played with a few different joins, but haven't been able to get the behaviour I want.

Is it possible to do this in a view?

What do I need to do to make it happen??

Thanks!

There is no such JOIN. SQL has a fixed column output: so you can't add arbritrary numbers of columns. It doesn't matter if it's a view, direct or in a stored procedure.

There are 2 main options

1. concatenate the many rows into one column which is a popular questions here on SO. One random solution using XML PATH

2. use dynamic SQL to add a column per row in a stored procedure.

Note: PIVOT is fixed column output too

Edit: for a maximum of 4 child rows

SELECT 
  P.col1, P.col2,
  C1.col1 AS Topic1,
  C2.col1 AS Topic2,
  C3.col1 AS Topic2,
  C4.col1 AS Topic4
FROM
  Parent P
  LEFT JOIN
  Child C1 ON P.Key = C1.FKey AND C1.ID = 1
  LEFT JOIN
  Child C2 ON P.Key = C2.FKey AND C2.ID = 2
  LEFT JOIN
  Child C3 ON P.Key = C3.FKey AND C3.ID = 3
  LEFT JOIN
  Child C4 ON P.Key = C4.FKey AND C4.ID = 4

You can use PIVOT too but I prefer the simpler self joins.

Take a look at PIVOT table functionality - e.g. http://www.help-sql.info/27/9/610208.html and http://blog.sqlauthority.com/2008/05/22/sql-server-pivot-table-example/

Although you will need to know the AssessmentId's before you can write the PIVOT