dynamic - Pivot Query into combinations of columns -

in sql server 2008 how can go here:

owner  animal   age  height   weight   -----  ------   ---  ------   ------  steve  dog        8     22      60  steve  cat        5     11      14  steve  gerbil     2     1.5     0.3   

to here:

owner  dog_age  dog_height  dog_weight  cat_age  cat_height  ... -----  -------  ----------  ----------  -------  ----------  ... steve       8           22          60        5          11  ...    

so pivoting animal column, want generate columns possible combinations of animals , columns. no aggregate function being performed. since table total number of possible combinations large, i'd solution avoids me having explicity type out every resulting column name, i'll if must.

i have looked @ several examples of pivot command, have not run across create columns combinations above, i'm not sure possible. sql server not area of expertise.

if don't care order of columns in final output:

declare @sql nvarchar(max);  select @sql = n'';  select      @sql = @sql + ',' + animal + '_$c$ = max(case when animal = '''            + animal + ''' $c$ end)' dbo.petstuff group animal;  select @sql = 'select owner '  + replace(@sql, '$c$', 'age')  + replace(@sql, '$c$', 'height')  + replace(@sql, '$c$', 'weight')  + ' dbo.petstuff  group owner;';  print @sql; -- exec sp_executesql @sql; 

if column order matters (order animal name age/height/weight alphabetically), can put columns in table instead , generate sql more work:

declare @cols table(col sysname); insert @cols select 'age' union select 'weight' union select 'height';  declare @sql nvarchar(max);  select @sql = n'';  select @sql = @sql + ',' + p.animal + '_' + col    + ' = max(case when animal = '''    + p.animal + ''' ' + c.col + ' end)' dbo.petstuff p cross apply @cols c group p.animal, c.col order p.animal, c.col;  select @sql = 'select owner ' + @sql  + ' dbo.petstuff  group owner;';  print @sql; -- exec sp_executesql @sql; 

can't think of way without dynamic sql.