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.

query

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!

Advertisements

Packing ^ A Necessary Evil ^

So. It has come to this. Packing. Cleaning. Moving. Unpacking. Losing necessities. Unpacking more. Finding necessities after rifling through many boxes. Giving up finding others because it just isn’t worth it anymore. Cleaning more. All with a cat and an army crawling, let’s get into everything baby. Blerg…Shoot me now.

never lose your packing list ^ brettandrachelplus

Brett and I are moving. And just not moving to another apartment, moving to a house. Our house. Our, we’re paying a mortgage on this place, I can actually paint walls, house. (I’ll post pictures of it all later.)

Anyhoo, with packing comes boxes. And with boxes comes forgetting what you packed in each box. This move, I have a plan, and like all brilliant plans, it started with a Pinterest search which resulted in me finding this pin. I thought that it looked like a pretty good idea, but not good enough. Instead of using paper and a number, I’m using a code for each room and a sharable spreadsheet. I thought that K01 would make more sense for a kitchen box rather than 301. Plus, multiple people packing can simultaneously add to the spreadsheet and it won’t get lost because it lives out in internet land. I then added a different tab for each different room so it wouldn’t look so jumbled.

The second method we’ve been using is a more avant-garde approach. Since we have been blessed with the luxury of being able to move to the new house slowly, we’ve been packing boxes, unpacking at the new house, then returning the boxes to be used again. Overall, it hasn’t been as stressful as it could be, I suppose.

packing spreadsheet

We’ll see how it ends up working for us. What methods have you used to stay organized while you move?