Dynamic Pivot Columns

CREATE TABLE PersonType
(
     [PersonTypeID] tinyint identity(1,1) primary key
    ,[PersonTypeName] varchar(30) default('')
    ,[Description] varchar(255) default('')
)
INSERT INTO PersonType([PersonTypeName], [Description])
VALUES('Geek','A person with an unusual or odd personality');
INSERT INTO PersonType([PersonTypeName], [Description])
VALUES('Hipster','Someone who rejects the established culture; advocates extreme liberalism in politics and lifestyle');
INSERT INTO PersonType([PersonTypeName], [Description])
VALUES('Nerd','An intelligent but single-minded expert in a particular technical field or profession');


CREATE TABLE Person
(
     [PersonID] int identity(1,1) Primary Key
    ,[PersonTypeID] tinyint
    ,[FirstName] varchar(50) default('')
    ,[LastName] varchar(50) default('')
    ,[Age]  AS (DATEDIFF(YEAR,[DateOfBirth],ISNULL([DateOfDeath],GETDATE()))) /* Too Lazy to calculate myself */
    ,[DateOfBirth] date
    ,[DateOfDeath] date
)
INSERT INTO Person(PersonTypeID, FirstName, LastName, DateOfBirth, DateOfDeath)
VALUES (1, 'Steve','Wozniak','1950-08-11', NULL)
INSERT INTO Person(PersonTypeID, FirstName, LastName, DateOfBirth, DateOfDeath)
VALUES (1, 'William Henry','Gates','1955-10-28', NULL)
INSERT INTO Person(PersonTypeID, FirstName, LastName, DateOfBirth, DateOfDeath)
VALUES (1, 'Richard William','Wheaton','1972-07-29', NULL)
INSERT INTO Person(PersonTypeID, FirstName, LastName, DateOfBirth, DateOfDeath)
VALUES (2, 'Jerome John','Garcia','1942-08-01', '1995-08-05')
INSERT INTO Person(PersonTypeID, FirstName, LastName, DateOfBirth, DateOfDeath)
VALUES (2, 'Steven Paul','Jobs','1955-02-24', NULL)
INSERT INTO Person(PersonTypeID, FirstName, LastName, DateOfBirth, DateOfDeath)
VALUES (2, 'Evil Steven Paul','Jobs','1955-02-24', NULL)
INSERT INTO Person(PersonTypeID, FirstName, LastName, DateOfBirth, DateOfDeath)
VALUES (3, 'Albert','Einstein','1879-04-18', '1955-05-18')
INSERT INTO Person(PersonTypeID, FirstName, LastName, DateOfBirth, DateOfDeath)
VALUES (3, 'Michelangelo','Simoni','1475-03-06', '1564-02-18')
INSERT INTO Person(PersonTypeID, FirstName, LastName, DateOfBirth, DateOfDeath)
VALUES (3, 'Tina','Brown','1953-11-21', NULL)
INSERT INTO Person(PersonTypeID, FirstName, LastName, DateOfBirth, DateOfDeath)
VALUES (3, 'Elizabeth Stamatina','Fey','1970-05-18', NULL)
INSERT INTO Person(PersonTypeID, FirstName, LastName, DateOfBirth, DateOfDeath)
VALUES (3, 'Fake Sarah Palin','Fey','1970-05-18', NULL)


/* Magic happens here.
We use the SQL STUFF function (http://msdn.microsoft.com/en-us/library/ms188043.aspx)
and the FOR XML clause (http://msdn.microsoft.com/en-us/library/ms190922.aspx)
to select out our PersonTypeName's

Result: [Geek],[Hipster],[Nerd]
*/
DECLARE @PersonTypeList VARCHAR(MAX);
SELECT @PersonTypeList = STUFF((SELECT '],[' + PersonTypeName FROM PersonType FOR XML PATH('')),1,2,'') + ']'

/* Pivot Time
Just a basic pivot here, we are getting the Age from Person, and then doing an inner join
to PersonType and doing a Pivot on the PersonTypeName.
 */
DECLARE @sql VARCHAR(MAX)
SET @sql =
'SELECT * FROM
(SELECT p.[Age], pt.PersonTypeName, p.PersonID FROM Person p
INNER JOIN PersonType pt on pt.PersonTypeID = p.PersonTypeID
) src
PIVOT (COUNT(PersonID) FOR PersonTypeName IN ('+@PersonTypeList+')) as pvt
ORDER BY [Age] ASC'

EXECUTE (@sql)
/*
Result:
Age     Geek    Hipster Nerd
39      1       0       0
41      0       0       2
53      0       1       0
56      1       2       0
58      0       0       1
61      1       0       0
76      0       0       1
89      0       0       1

Cool you say, but I could of just coded those PersonType's into the sql and it would of done the same.
Ok, but what about when we add a new PersonType ?
*/
INSERT INTO PersonType([PersonTypeName], [Description])
VALUES('Jock','A person trained to compete in sports');
INSERT INTO Person(PersonTypeID, FirstName, LastName, DateOfBirth, DateOfDeath)
VALUES (4, 'Michael Jeffrey','Jordan','1963-02-17', NULL)

/*
Well lets see what happens
*/
SELECT @PersonTypeList = STUFF((SELECT '],[' + PersonTypeName FROM PersonType FOR XML PATH('')),1,2,'') + ']'
SET @sql =
'SELECT * FROM
(SELECT p.[Age], pt.PersonTypeName, p.PersonID FROM Person p
INNER JOIN PersonType pt on pt.PersonTypeID = p.PersonTypeID
) src
PIVOT (COUNT(PersonID) FOR PersonTypeName IN ('+@PersonTypeList+')) as pvt
ORDER BY [Age] ASC'

EXECUTE (@sql)

/*
Result:
Age Geek    Hipster Nerd    Jock
39  1       0       0       0
41  0       0       2       0
48  0       0       0       1
53  0       1       0       0
56  1       2       0       0
58  0       0       1       0
61  1       0       0       0
76  0       0       1       0
89  0       0       1       0

Holy shitballs batman, it is automatically getting the PersonTypes!
*/