CODEWITHSUNDEEP
sqlsql-serverms-accesssql-server-2000
Dragg
Dragg

Reputation: 83

SQL Query in an 1:N relationship

I have the following database, it's a simple relationship between an user and his receipts:

Database

I need to get as many row as users and so many column as much receipt it have. For example:

If user 1 have 2 receipt I have to obtain:

IdUser   Receipt 1    Receipt 2
  1         12€          13€

I tried with an inner joint between User and Receipt tables, but I have as many rows users as receipts they have.

EDIT

SELECT user.idUser, receipt.value
FROM user INNER JOIN receipt ON user.IdUser = receipt.IdUser;

Upvotes: 1

Views: 5942

Answers (1)

Pelin
Pelin

Reputation: 966

If you know the maximum number of the receipts you can get the result with the pivot query below:

SELECT * FROM (
SELECT   ROW_NUMBER() OVER(PARTITION BY receipt.IdUser  ORDER BY receipt.IdUser ASC) AS RowId, user.IdUser, receipt.value
  FROM user  INNER JOIN receipt ON user.IdUser = receipt.IdUser 
  )
 as s
PIVOT
(
    max(value)
    FOR RowId IN ([1], [2], <max number of the receipts> )
)AS pvt

But if you don't know the max number of the items it is better to use dynamic pivot

SQL Server - Dynamic PIVOT Table - SQL Injection

Upvotes: 1

Related Questions