Dynamic Pivot Columns in SQL Server

I have a table named Property with following columns in SQL Server:

Id    Name

there are some property in this table that certain object in other table should give value to it.

  • Two radically different queries against 4 mil records execute in the same time - one uses brute force
  • SQL Server - using the WITH clause in an INSERT statement
  • Working with Decimal fields in SSIS
  • SQL Server 2008 timestamp data type
  • Error in SQL Server query - inner join
  • Grouping total active users for each of the previous 8 weeks
  • Id    Object_Id    Property_Id    Value
    

    I want to make a pivot table like below that has one column for each property I’ve declared in 1’st table:

    Object_Id    Property1    Property2    Property3    ...
    

    I want to know how can I get columns of pivot dynamically from table. Because the rows in 1’st table will change.

  • SQL server - Pivot only on a part of the result
  • Transpose rows and columns without aggregate
  • SQL Server PIVOT with multiple X-axis columns
  • Pivot a table on a value but group the data on one line by another?
  • PIVOT in sql 2005
  • How to create query in sql to pivot data?
  • One Solution collect form web for “Dynamic Pivot Columns in SQL Server”

    Something like this:

    DECLARE @cols AS NVARCHAR(MAX);
    DECLARE @query AS NVARCHAR(MAX);
    
    select @cols = STUFF((SELECT distinct ',' +
                            QUOTENAME(Name)
                          FROM property
                          FOR XML PATH(''), TYPE
                         ).value('.', 'NVARCHAR(MAX)') 
                            , 1, 1, '');
    
    SELECT @query =
    
    'SELECT *
    FROM
    (
      SELECT
        o.object_id,
        p.Name,
        o.value
      FROM propertyObjects AS o
      INNER JOIN property AS p ON o.Property_Id = p.Id
    ) AS t
    PIVOT 
    (
      MAX(value) 
      FOR Name IN( ' + @cols + ' )' +
    ' ) AS p ; ';
    
     execute(@query);
    

    SQL Fiddle Demo.

    This will give you something like this:

    | OBJECT_ID | PROPERTY1 | PROPERTY2 | PROPERTY3 | PROPERTY4 |
    -------------------------------------------------------------
    |         1 |        ee |        fd |       fdf |      ewre |
    |         2 |       dsd |       sss |      dfew |       dff |
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.