Perhaps this has already been asked (or scoffed at for being 'undocumented') but have you considered a function using COALESCE to return your pivoted series?

CREATE TABLE Test (

id INT,

val VARCHAR(10))

INSERT Test (id,...