How to Combine Multiple Rows into 1 column from results of initial query

Question

I have a query, where i'm pulling invoice data for some exceptions. We've noticed some theft when a specific service type is tendered.:

Select Username, Service, Time, invoice#
From Invoice_Tb
Where Service_Category = 'Service_Type1'

Output:

Username      Service      Time              Invoice#
Bob           Service1     3/1/17 4:30AM     1234
Bobby         Service2     3/1/17 5:30AM     1633
Jim           Service2     3/7/17 5:45AM     1894

What I'd like to do is grab some additional information from "Invoice_Detail" table, based on the results from my first query. We've found auditing all of the records, is quite arduous. So, i'd like to see what additional services were on these invoices.

Example of the output i'm looking for:

Username      Service      Time              Invoice#    Other_Services
Bob           Service1     3/1/17 4:30AM     1234        Service1, sv7, sv8
Bobby         Service2     3/1/17 5:30AM     1633        Service2, sv9, sv5
Jim           Service2     3/7/17 5:45AM     1894        Service2, sv7, sv9

Keep in mind, the Invoice_Detail_Tb lists the details on separate line items - always listing the same invoice#.

I can - but i'm unsure how I would combine the rows of the data from invoice_detail_tb, as it would list it as separate rows - Service 1 (1 Row), sv7 (2nd row), etc. — Shmewnix, Mar 14 '17 at 14:33
Check this http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server — Rahul, Mar 14 '17 at 14:34

morinx · Accepted Answer · 2017-03-14T14:50:26.510

You can Join the two tables:

Select 
    a.Username, 
    a.Service, 
    a.Time, 
    a.invoice#,
    b.other_services
From Invoice_Tb a JOIN invoice_detail_tbl b
    ON a.invoice# = b.invoice#
Where Service_Category = 'Service_Type1'

if "other_services" is a combination of other queries, you can change this to:

Select 
    Username, 
    Service, 
    Time, 
    a invoice#,
    other_services = STUFF((SELECT ',' + b.service
      FROM invoice_detail_tbl b WHERE b.invoice# = a.invoice#
      FOR XML PATH(''), TYPE).value(',', 'NVARCHAR(MAX)'), 1, 1, '')
From Invoice_Tb a
Where Service_Category = 'Service_Type1'

I did, and it didn't mention that other_services is not a column. — morinx, Mar 14 '17 at 14:39

How to Combine Multiple Rows into 1 column from results of initial query

1 Answers1