1

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.LastName, 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!

laura
  • 2,951
  • 9
  • 44
  • 61

2 Answers2

3

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.

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • I was afraid someone would say that... Given that the number of topics is fixed to a maximum of four, can I generate a view to satisfy my requirements. The data is to be used by a fixed package, I'm looking into the dynamic SQL option now, thanks. – laura Feb 02 '11 at 11:16
  • Cheers, that looks good. Is there any way to get around the hardcoded ids?? – laura Feb 02 '11 at 12:00
  • Maybe if I defined a SEQ column, and defined it 1...4? – laura Feb 02 '11 at 12:09
  • 1
    @lainie: yes, you'd need something to identify a child row. Check out PIVOT too in case it works better for you to avoid adding a column – gbn Feb 02 '11 at 12:15
  • 1
    @lainie: I think you might want to take a look at `ROW_NUMBER()`: http://msdn.microsoft.com/en-us/library/ms186734.aspx – Andriy M Feb 02 '11 at 13:07
2

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

Stuart
  • 66,722
  • 7
  • 114
  • 165