SQL select query - display related records in onr column

Question

I have these tables Tables I want to have a result like this

╭────────────┬────────────┬──────┬────────────────────────┬────────────────────────╮
│ InvoiceID  │    Date    │ Sum  │ SUM(RecietInvoice.Sum) │ RecietInvoice.RecietID │
├────────────┴────────────┴──────┴────────────────────────┴────────────────────────┤
│      1     │ 11/11/2010 │ 1200 │           1200         │        34,45           │
╰────────────┴────────────┴──────┴────────────────────────┴────────────────────────╯

In the RecietInvoice.RecietID I want to have all the RecietID for this Invoice.

Have a look at http://i.imgur.com/goWIq.png I want to have lines 29,30,31 as a single row with RecietID as "7,8,9" — Kulpemovitz, Jan 30 '12 at 20:54
If you need help with the comma separated list, try this approach: http://stackoverflow.com/questions/3024456/displaying-a-field-as-a-comma-separated-list-in-reporting-services-2005 — Jamie F, Jan 30 '12 at 20:58
I'm wondering what the 10x means at the end. (I'm thinking I get 10 times the number of interwebs.) — Hogan, Jan 30 '12 at 21:04
@JohnSaunders - That bugged me too - I had to make my spelling like his for the example. — Hogan, Jan 30 '12 at 21:20

Hogan · Accepted Answer · 2012-01-30T22:37:47.360

2

Here is one way (untested might have typos)

Select I.InvoiceID, I.Date, RI.Sum, 
   SUM(R.CashTotal) OVER(PARTITION BY  I.InvoiceID) 
     stuff( (SELECT ', '+CAST(RecietR2.RecietID as varchar(max))
             FROM  Reciet R2
             WHERE RecietR2.InvoiceID  = I.InvoiceID
             FOR XML PATH ('')
           ), 1, 2, '') as RecietIDs
FROM Invoice I
LEFT JOIN RecietInvoice RI ON I.InvoiceID = RI.InvoiceID
LEFT JOIN Reciet R ON RI.RecietID = R.RecietID

edited Jan 30 '12 at 22:37

answered Jan 30 '12 at 20:58

Hogan

69,564
10
76
117

Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'ORDER'. – Kulpemovitz Jan 30 '12 at 21:17
Sounds like you have incorrect syntax... paste what you did (maybe pastebin is a good spot) – Hogan Jan 30 '12 at 21:18
@Kulpemovitz Ok, I saw one typo, I did not have a cast. I took out the order by because who cares? Try this version. – Hogan Jan 30 '12 at 21:24

SQL select query - display related records in onr column

1 Answers1