multiple result query as csv string in new column

Question

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 products connected to user by user2product table separated with comma.

Are you wanting to write this in T-SQL or in Linq? – msarchet Mar 31 '11 at 14:19 — msarchet, Mar 31 '11 at 14:19

score 0 · Answer 1 · edited May 23 '17 at 12:04

0

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

edited May 23 '17 at 12:04

Community

1
1

answered Mar 31 '11 at 14:29

Thilo

8,827
2
35
56

Just saw the SQL-Server tag, thus it probably won't be Postgres. Still, the idea should hold. – Thilo Mar 31 '11 at 14:31

score 0 · Answer 2 · answered Mar 31 '11 at 14:34

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

multiple result query as csv string in new column

2 Answers2