Representing one-many data in a query in one row in sql server?

Question

I have a database with two tables: Users, Items

Users looks like this:

userId     uniqueindentifier
username   nvarchar

Items looks like this:

userId     uniqueidentifier
itemName   nvarchar

So each user can have several items.

I need to do a query that returns a user. I could do something like this:

SELECT * FROM Users JOIN Items on Users.UserId = Items.UserId
WHERE UserId = 1

If user 1 has 2 items, the query will return something like this:

UserId    userName    ItemName
1         Oliver      Apple
1         Oliver      Orange

I want the query to return something like this:

UserId    userName    ItemNames
1         Oliver      Apple, Orange

Or even better, something like this:

UserId    userName    ItemName1    ItemName2
1         Oliver      Apple        Orange

Is it possible to achieve this? The reason I want to do it this way is because if I do a query with SELECT TOP 50, I want to return 50 distinct users, not (for example) 18 users in 50 rows, with several items per user.

Answer 1

You could use PIVOT and ROW_NUMBER to pivot on if there was a known max number of columns. Or XML PATH to simulate GROUP_CONCAT for the delimited list. Both of these are covered in other SO questions.

You don't need to do this though to achieve the result stated in your last sentence. You can just do

;WITH TopUsers As
(
SELECT TOP (50) *
FROM Users
ORDER BY UserId
)

SELECT * 
FROM TopUsers 
    JOIN Items on TopUsers.UserId = Items.UserId