Using XML PATH() to create a list of values

[TamarGranor]

SQL Server 2008 R2

I'm trying to understand the FOR XML PATH('') and how it's used when you want to combine multiple values from a column into a single record. To experiment, I'm using the AdventureWorks database.

My simple example is to get a list of orders with a comma-separated (or semi-colon separated) list of the items in that order. I'm modeling my code after what I found here:

[URL unfurl="true"]http://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/[/url]

Specifically, at this point, I'm up to what is titled "Example 1". But something's not working.

When I use this code:

SELECT PurchaseOrderID, 
       (SELECT ';' + Name
          FROM Production.Product
          WHERE Purchasing.PurchaseOrderDetail.ProductID = Production.Product.ProductID
          FOR XML PATH('')) OrderProducts
FROM Purchasing.PurchaseOrderDetail
GROUP BY PurchaseOrderID
ORDER BY 1

I get the error:

Msg 8120, Level 16, State 1, Line 4
Column 'Purchasing.PurchaseOrderDetail.ProductID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Not surprisingly, if I remove the GROUP BY, I get one row per product.

I know there are other ways to solve this particular problem, but I'm trying to understand this approach. What am I missing?

Tamar

 

[gmmastros]

It took me a while to figure this out.

You want a comma separated list of items for each order id, right?

The problem is this:

WHERE Purchasing.PurchaseOrderDetail.ProductID = Production.Product.ProductID

Your correlated sub query is joining to the outer query on product id, which is not what you want. You want the correlated sub query to join to the outer query on purchase order id. Unfortunately, your inner query doesn't have access to the PurchaseOrderId, so you need to join to another table. Like this:

SELECT PurchaseOrderID, 
       (  SELECT ';' + Name
          FROM Production.Product
[!]               Inner Join Purchasing.PurchaseOrderDetail As A
                  On Production.Product.ProductID = A.ProductID[/!]
          WHERE Purchasing.PurchaseOrderDetail.[blue]PurchaseOrderID = A.PurchaseOrderID[/blue]
          FOR XML PATH('')) OrderProducts
FROM Purchasing.PurchaseOrderDetail
GROUP BY PurchaseOrderID
ORDER BY 1

This query will give you the semi-colon delimited list you want.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

By the way.... To help me figure this out, I wrote this query...

; With Data As
(
  Select  POD.PurchaseOrderID,P.Name
  From    Production.Product As P
          Inner Join Purchasing.PurchaseOrderDetail As POD
            On P.ProductID = POD.ProductID
)
Select  PurchaseOrderID,
        ( SELECT ';' + Name
          FROM   Data As A
          WHERE  Data.PurchaseOrderID = A.PurchaseOrderID
          FOR XML PATH('')) OrderProducts
From	Data
Group BY Data.PurchaseOrderID

The CTE at the top basically gave me a single "table" that had PurchaseOrderId and ProductName. Once I did this, I realized I needed to have PurchaseOrderId attached to each row in the inner query.

By the way... this query, and the one I posted in my earlier reply have exactly the same execution plan. I think I like this version a little better because it is easier for me to understand what's going on. Your mileage may vary.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TamarGranor]

Thanks, George. Took me a while, but I finally saw the point about why this needed the PurchaseOrderDetail table in the subquery. I found a better (simpler) example using the Customer and PurchaseOrder tables. Once I got this working, I spent a little time reading about and playing with FOR XML more generally.

(Ultimate goal here is to do some writing about things you can do easily in SQL Server that are hard or impossible with VFP's SQL.)

Tamar