SQL Query in an 1:N relationship

Question

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

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;

Personally, rather than using Dynamic SQL, I would recommend doing this type of transformation in your presentation layer. For example, by using a Matrix in SSRS, or a Pivot Table in Excel. — Thom A, Feb 23 '18 at 11:06
Will you be returning more than 1 User in a query? If you have to do this using T-SQL, and not a presentation layer, then **Consumable** sample data is going to really help you get an answer. — Thom A, Feb 23 '18 at 11:30

score 1 · Accepted Answer · answered Feb 23 '18 at 12:44

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

Thank you for your answer but I have sql server 2000 and ROW_NUMBER() OVER(PARTITION BY) it's not implemented, sorry I didn't specify version. In other version works fine — Dragg, Feb 26 '18 at 09:03

SQL Query in an 1:N relationship

1 Answers1