multiple result query as csv string in new column

I ve got 3 tables:

User, product user2product

each product has got ID.

Is it possible to write query in which as result I would get 2 columns:

UserID, Products

And in products column I have all pr开发者_Go百科oducts connected to user by user2product table separated with comma.

what SQL-Dialect are we talking about? For postgresql, there is a pretty comprehensive answer to be found here: How to concatenate strings of a string field in a PostgreSQL 'group by' query?

For other SQL-Systems the idea should be pretty much the same. You would have to search for the correct aggregate function, everything else can be done using a simple group by directive.

Cheers Thilo

Its quite possible to do this using in native tsql using the for xml path statement as detailed here

To access this through linq create a stored procedure on the db, drag it into your dbml file and then call it from the data context like a method.

The code for the sp would look something like this

select
    u.userid,
    substring(( select ',' + cast(p.productid as nvarchar(20))
        from
            user2product up inner join
            product p on up.productid = p.productid
        where
            up.userid = u.userid
        for xml path('')),2,2000000) as Products
from
    users u