Hola tengo una mesa así:

Building    Categories      Properties
-----------------------------------------
Building250 Structure       Steel
Building250 Security        Access Card
Building250 Security        Security Guard
Building250 Security        Fire Alarm System 
Building250 Security        Sprinklered
Building250 Security        Smoke or heat detectors
Building46  Structure       Brick
Building46  Structure       Steel
Building46  Walls           Steel Stud
Building46  Walls           Masonry
Building46  Washroom        OwnSpace
Building46  Washroom        Common
Building46  Security        Access Card
Building46  Security        Burglar Alarm

y necesito girarlo así:

Building    Structure   Security           Walls       Washroom
----------------------------------------------------------------------------
Building250 Steel       Access Card     
                        Security Guard      
                        Fire Alarm System       
                        Sprinklered     
                        heat detectors      
Building46  Brick       Access Card       Steel Stud    OwnSpace
            Steel       Burglar Alarm     Masonry       Common 
                        Sprinklered     

Intenté SQL Pivot, pero dado que requiere una agregación, devuelve solo 1 propiedad para una categoría determinada. ¿Hay otra manera?

Aquí está mi SQL Pivot:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Category) 
                  FROM Buildings
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Building, ' + @cols + ' from 
             (
                SELECT Building, Category, Property, from Buildings
            ) x
            pivot 
            (
                min(Property)
                for Category in (' + @cols + ')
            ) p '

execute(@query)