SQL Server retrieve list of rows inside another list of rows from database

Question

This might sound confusing and easy also. But I tried a few ways and it didn't work

Now i have a table User with these columns

• Id
• UserId
• FileId
• Text

and another table File

• Id
• Filename

and a 3rd table UserDetails

• Id
• Name
• LastName

User & File are linked using foreign key FileId in matching to Id similarly User and UserDetails are linked with foreign key UserId and Id.

So I created a view in SQL:

CREATE VIEW [dbo].[vw_NewView] AS
    SELECT 
        U.Text as Text,
        UD.Name + ' ' + UD.LastName AS FullName
    FROM 
        User U
    LEFT OUTER JOIN 
        dbo.UserDetails UD on UD.Id = U.UserId

Now I get a result set:

Text     FullName
entry 1  NAme1
entry 2  Name2

Note: each User will be associated with one UserDetails, i.e. User has a one-to-one relationship with Userdetails.

But User has a one-to-many relationship with File, so every User can have multiple files.

How can I retrieve these files for each user?

Something like this

Text     FullName   FileName 
entry 1  NAme1      File1
                    File2 
entry 2  Name2      File1
                    File2
                    File3

Answer 1

I used Stuff to get the desired output, Below is the code.

 CREATE VIEW [dbo].[vw_NewView] AS
    SELECT 
        U.Text as Text,
        UD.Name + ' ' + UD.LastName AS FullName,
     SUBSTRING(
            (
                SELECT ','+CAST(U.Id AS VARCHAR) +'_'+F.FileName  AS [text()]
                FROM File F
                WHERE  U.Id = F.NoteId
                ORDER BY U.Id
                FOR XML PATH ('')
            ), 2, 1000) [FileName]
    FROM 
        User U
    LEFT OUTER JOIN 
        dbo.File F on UD.Id = U.NoteId

    GO

This worked For me. Got my answer as a string of comma separated values.

output:

Text     FullName   FileName 
entry 1  NAme1      File1,File2 
entry 2  Name2      File1,File2,File3