Friday, May 28, 2010

Concatenating String Fields In T-SQL

Lets say you've got a table called 'metaproperty' with an NVARCHAR(50) field called 'names'. How do you get all the values of the 'names' field concatenated into one string, separated by commas?

Like this:

select STUFF(
(SELECT N',' + names
FROM (SELECT * FROM metaproperty) AS Y
--ORDER BY names
FOR XML PATH('')),
1, 1, N'')

The ORDER BY is optional, as are any DISTINCTs you might want to shove in...

Thursday, May 27, 2010

PIVOT

Found these pages quite interesting but not managed to make use of it yet:
It's about how to use PIVOT. Have a look round his site and look for the UNPIVOT pages, much geeky fun to be had within a T-SQL server (2005 onwards.)

[UPDATE] The key thing I've found (as highlighted in the first link) is that the PIVOT statement operates upon the fields returned in the FROM clause, NOT the SELECT clause. This is important because it means, if you have a JOIN or two, you probably have a lot more fields to quote in your clause than you think. The solution, I found, is to nest the original query inside the FROM( ) AS virtualtable thereby reducing the pivot to only the fields you're concerned with.

eg:

select *
from
(
select
distinct top 100
v.pointid, v.doublevalue, p.displayname
from [property] p
inner join pointvalue v on p.propertyid = v.propertyid
inner join point pt on v.pointid = pt.pointid
where v.pointid in (select top 5 p.pointid from point p where p.instanceid = 36132)
) virtualtable
pivot
(
sum(doublevalue)
for [displayname] in ([Low Price], [High Price])
) as alias

Friday, May 21, 2010

Virtual Machines

My foray into the Mac world continues, though this post isn't specifically a Mac oriented thing. I have been looking for virtual machine applications...

Firstly, what Wikipedia has to say on the subject:
And the virtual PC applications I have used, or rather, had closer experience of than the vast array of virtualisation software out there:
And finally, my personal opine on the subject:
  • VirtualPC - Quite good, does what it says on the tin but hogs a lot of system resources. Will run non-Windows OS's, but probably is better running Windows. Discontinued, I understand, in deference to Hyper-V.
  • Parallels - Not the same kind of virtual machine as VirtualPC, in that it's expressly designed to run Windows on a Mac, but it does this beautifully. It can startup Windows which has been installed into Parallels or BootCamp and run them side by side.
  • BootCamp - Runs Windows on a Mac as a stand-alone OS, in that Mac OS X is not running at the same time. Allows the Mac to operate as a Windows PC and does it pretty much better than a regular PC can IMHO.
  • VMWare - Basically just like VirtualPC, but many would have it as being more stable and not Microsoft.

Web.config Element Positions

One thing that bugs me from time to time is that I cannot remember where the hell a web.config element is supposed to go. Some (ok, all) of them are extremely location sensitive. Some don't really care as long as certain elements appear before, but not necessarily immediately before, them.

Here are some which often (in the grand scheme of things, but not every day) annoy me and links to find others which might cause problems:

Thursday, May 20, 2010

I Don't Know Nout About Fonts, But I Know This

This is awesome - I've seen it tweeted recently, a lot, but it really is impressive: