STUFF() And Not Your Face ^ A Fun SQL Tidbit

Okay, I’m probably going to lose some people here, but maybe not. Who am I kidding, of course I will. No one wants to read about SQL functions except for nerds. And I’m not talking about the “oh, I watch The Big Bang Theory! I’m such a nerd!” types. (Don’t get me started. I’ve got strong words to say about the type. . . wannabe nerds more like it. . . but I digress. Much like Victor Hugo in Les Mis. (DANGIT VICTOR HUGO! NO MORE CRAZY NUNS!))

ANYHOO. I’m really not going to talk about technical things very often here because I’m pretty sure I’m writing this totally for my benefit, but I had to share this because I thought that it was super cool. For those of you who don’t know, I write queries at work. I also administrate monitoring software and make reports using queries I’ve written. I also begrudgingly code here and there. Yes, I know I lead the glamorous lifestyle. Not everyone’s job can be so high profile and exciting.

So basically I needed multiple rows of data from one column to be concatenated into one row of data based on their location. I’m going to explain it in pies, bakers, and bakeries. I had multiple bakeries where multiple bakers worked. Each baker could make a pie with two different types of crust and a variety of fillings. I wanted the fillings for each type of pie crust to all be in one row, not one per row. But this turns out to be a very tricky thing to do, especially when each column of data you want lives in their own tables. (Sorry, couldn’t think of a pie analogy for that.)

How the data was represented before changes were made.

How the data was represented before changes were made.

So like anyone who has a problem they can’t solve on their own, I turned to Google. Upon much digging around, I came across this one little function that seemed very promising, the STUFF() function along with XML PATH(). The stinky thing was that all the examples I found were too simplistic and I had to break out on my own. Here’s what I came up with.


The biggest hurdle I had to get over was figuring out the where clause of the subquery. All the examples I found showed the where being what you were wanting to combine. At first I kept using the PieFillingID in the where, but that would return all fillings for all crusts every time. Whoops. So I used bakery and baker which did the trick. **Update** Upon piddling with it some more I realized I needed to also add “and p.piecrust = p2.piecrust” to the where within the subquery. I noticed that for different pie crusts for the same baker, it was putting all the fillings into both instead of sorting them out based on the crust. This addition fixed that.

Pretty comma separated lists where applicable!

Pretty comma separated lists where applicable!

Thanks for indulging me on my SQL post!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s