Archive for September 2007
Two days I needed to take a block of records and turn the rows into a horizontal comma separated list. That comma separated list in turn needed to be the value for a column in a larger query.
I googled and found quite a few potential solutions and finally settled on using the COALESCE function. You certainly could use temp tables or cursors accomplish this. But the COALESCE function, with help from Arian and a bit of my tweaking ended up being a very succinct and easy to read solution.
DECLARE @list NVARCHAR(MAX)
SELECT @list = COALESCE(@list+’, ‘,”)+[FirstName] +’ ‘+[LastName]
Of course there were some joins I needed to do in the FROM clause to get the correct data and it was also parameterized, but this was the gist of it.
I pushed the above snippet into a function, changed the last SELECT statement to a RETURN statement(for the sake of the function) and then used that function in the larger query to show the list of people as a comma separated list. It worked just as intended and solved the issue at hand.
My son was showing us a mosquito bite on his knee this evening and right after that I asked if he was ready to go to bed. He was still thinking about the mosquito and said “bite me” quite loud.
So the conversation flowed like this:
Dad: “Are you ready to go night night?”
2 year old: “Bite me”
Two weeks ago my brother-in-law and I found a new kind of kite called the Conyne and built it from scratch with dowel rods, thread, white glue, electrical tape and tissue paper.
We build it from about 8:00pm – 12:00 am and test flew it up and down main street from about 12:00 am – 1:00 am. But we couldn’t get enough wind while he was here to really put it up.
So yesterday finally there was enough wind to put it up and boy did it fly. It pulled so hard the cord cut into my hands pretty good. Its amazing how much stress tissue paper can withstand.
Finally Chipotle opened its doors today in downtown Iowa City. I’ve been missing their carnitas fajita burrito since moving from Colorado Springs. I tried to find a good substitute, but Pancheros never really cut it for me.
Anyway we went downtown tonight and waited in a good sized line (almost ran outside the door) but it was well worth it. The carnitas was salty with a light lemon flavor. There is no carnitas like it in this part of Iowa.
If you live in the area, Chipotle is downtown at the Old Capitol Mall almost right across from Pancheros.
March and I have been married 5 happy years today. And baby no. 2 is due in about 10 days as well.
I love you babe.
It’s mid September and we have the first real feel of Fall in the air. The temperature really dropped this week and we’ll likely have a good frost in the next couple of days.
This is good for two main reasons. Iowa is incredibly pleasant with these cooler temperatures and the frost means the ragweed loses its ability to make me miserable.