Representing one-many data in a query in one row in sql server?
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:
UserI开发者_StackOverflow社区d 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.
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
精彩评论