Combine multiple rows into one column to display in another query result?

Question

I'm trying to make a general report query for our stores where we can see all the sales and which stores the sales took place in. Here's the query I have built so far:

SELECT      Customer.LastName + ', ' + Customer.FirstName AS 'Customer Name',
            Store.Name AS 'Store', 
            Item.ItemCode + ': ' + Item.Name AS 'Item', 
            Sale.SaleID AS 'Sale ID',
            Sale.SaleDate 'Date',
            Sale.SubTotal AS 'Subtotal', 
            Sale.GST AS 'GST'
            Sale.SaleTotal AS 'Sale Total'
FROM        Sale
            INNER JOIN Customer ON Sale.CustomerID = Customer.CustomerID
            INNER JOIN Store ON Sale.StoreID = Store.StoredID
            INNER JOIN SaleItem ON SaleItem.SaleNumber = Sale.SaleNumber
            INNER JOIN Item ON Item.ItemCode = SaleItem.ItemCode
WHERE       Sale.SaleDate >= DATEADD(MONTH, -1, GETDATE()) 
ORDER BY    Sale.SaleDate DESC

Now, the result of the above query looks something like this (I'll put it in code format because stackoverflow doesn't allow tables):

<table>
        <tr>
            <td>Customer Name</td>
            <td>Store</td>
            <td>Item</td>
            <td>Sale ID</td>
            <td>Date</td>
            <td>Subtotal</td>
            <td>GST</td>
            <td>Sale Total</td>
        </tr>
        <tr>
            <td>John Smith</td>
            <td>Store No. 1</td>
            <td>001: Item ABC</td>
            <td>1</td>
            <td>2014-12-18</td>
            <td>100.00</td>
            <td>5.00</td>
            <td>105.00</td>
        </tr>
        <tr>
            <td>John Smith</td>
            <td>Store No. 1</td>
            <td>002: Item DEF</td>
            <td>1</td>
            <td>2014-12-18</td>
            <td>100.00</td>
            <td>5.00</td>
            <td>105.00</td>
        </tr>
        <tr>
            <td>John Smith</td>
            <td>Store No. 1</td>
            <td>003: Item GHI</td>
            <td>1</td>
            <td>2014-12-18</td>
            <td>100.00</td>
            <td>5.00</td>
            <td>105.00</td>
        </tr>
    </table>

So what I really want to do is combine those sale items into one column so it can be displayed like this, for example:

<table>
        <tr>
            <td>Customer Name</td>
            <td>Store</td>
            <td>Item</td>
            <td>Sale ID</td>
            <td>Date</td>
            <td>Subtotal</td>
            <td>GST</td>
            <td>Sale Total</td>
        </tr>
        <tr>
            <td>John Smith</td>
            <td>Store No. 1</td>
            <td>001: Item ABC; 002: Item DEF; 003: Item GHI</td>
            <td>1</td>
            <td>2014-12-18</td>
            <td>100.00</td>
            <td>5.00</td>
            <td>105.00</td>
        </tr>
    </table>

I honestly have no clue how to do this as I'm still fairly new to SQL (only been doing it for 1.5 years) but I'm fairly certain this will require some sort of a subquery.

Also, as a note, this actual result would be much bigger as it would contain all the different sales from the month. So I want to combine the sale items for each sale in the row for that sale.

Any help is appreciated! Thanks!

EDIT: I'm using SQL Server

EDIT2: I also tried using XML PATH like this:

SELECT      Customer.LastName + ', ' + Customer.FirstName AS 'Customer Name',
            Store.Name AS 'Store', 
            (SELECT 
                    STUFF((SELECT '; ' + Item.ItemCode + ': ' + Item.Name
                          FROM  Item
                                INNER JOIN SaleItem ON SaleItem.ItemCode = Item.ItemCode
                          WHERE SaleItem.SaleID = Sale.SaleID
                          FOR XML PATH('')), 1, 1, '') [Item Code/Name]
                FROM    SaleItem
                        INNER JOIN Sale ON SaleItem.SaleID = Sale.SaleID
                WHERE   Sale.SaleID = SaleItem.SaleID
                GROUP BY Sale.SaleID, SaleItem.SaleID) AS 'Item', 
            Sale.SaleID AS 'Sale ID',
            Sale.SaleDate 'Date',
            Sale.SubTotal AS 'Subtotal', 
            Sale.GST AS 'GST'
            Sale.SaleTotal AS 'Sale Total'
FROM        Sale
            INNER JOIN Customer ON Sale.CustomerID = Customer.CustomerID
            INNER JOIN Store ON Sale.StoreID = Store.StoredID
            INNER JOIN SaleItem ON SaleItem.SaleNumber = Sale.SaleNumber
            INNER JOIN Item ON Item.ItemCode = SaleItem.ItemCode
WHERE       Sale.SaleDate >= DATEADD(MONTH, -1, GETDATE()) 
ORDER BY    Sale.SaleDate DESC

However, the subquery I made there returns a combination of all the sale items for every single sale. So it's kind of like this:

Row1: 001: Item ABC; 002: Item DEF; 003: Item GHI
Row2: 005: Item MNO; 006: Item PQR; 007: Item STU
Row3...
Row4...
Row5...etc.

So the big query I have won't even execute because the subquery returns more than one row

Could you tell us which DB do you use: SQLServer, MySQL, ... ? — , Dec 18 '14 at 21:57
You can use [XML Path](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string). — Ryan B., Dec 18 '14 at 22:28
@Greenspark yes, I tried that too. The problem is, it returns a combination of all the sale items for ALL the sales. So that subquery ends up returning more than one row. I can't figure out how to make it so that the subquery would check and only return the combination of the sale items relating to the current saleid during the execution of the whole query. — David Flynn, Dec 18 '14 at 22:33

score 0 · Accepted Answer · answered Dec 18 '14 at 23:16

Solved the issue. I was pretty close when I was playing around with the XML PATH. Here is what I ended up with:

    SELECT      Customer.LastName + ', ' + Customer.FirstName AS 'Customer Name',
            Store.Name AS 'Store', 
            STUFF((SELECT '; ' + Item.ItemCode + ': ' + Item.Name
                   FROM  Item
                         INNER JOIN SaleItem ON SaleItem.ItemCode = Item.ItemCode
                   WHERE SaleItem.SaleID = Sale.SaleID
                   FOR XML PATH('')), 1, 1, '') [Item Code/Name]
            FROM    SaleItem
                    INNER JOIN Sale ON SaleItem.SaleID = Sale.SaleID
            WHERE   Sale.SaleID = SaleItem.SaleID
            GROUP BY Sale.SaleID, SaleItem.SaleID) AS 'Item', 
            Sale.SaleID AS 'Sale ID',
            Sale.SaleDate 'Date',
            Sale.SubTotal AS 'Subtotal', 
            Sale.GST AS 'GST'
            Sale.SaleTotal AS 'Sale Total'
FROM        Sale
            INNER JOIN Customer ON Sale.CustomerID = Customer.CustomerID
            INNER JOIN Store ON Sale.StoreID = Store.StoredID
            INNER JOIN SaleItem ON SaleItem.SaleNumber = Sale.SaleNumber
            INNER JOIN Item ON Item.ItemCode = SaleItem.ItemCode
WHERE       Sale.SaleDate >= DATEADD(MONTH, -1, GETDATE()) 
ORDER BY    Sale.SaleDate DESC

That query displays exactly what I wanted. Thanks for whoever commented anyways.

Combine multiple rows into one column to display in another query result?

1 Answers1