10

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.

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.

John Smith
  • 7,243
  • 6
  • 49
  • 61
Ashkan
  • 1,643
  • 5
  • 23
  • 45

1 Answers1

19

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 |
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • Thanks But another question. why you wrote MAX(VALUE)? – Ashkan Feb 10 '13 at 17:08
  • 2
    @AshkanAleAli You have to use an aggregate function for the pivoted values, I used `MAX` as a work around. – Mahmoud Gamal Feb 10 '13 at 17:42
  • and final question! Is there any way that I can turn this to some kind of view? you can't use Declare... in view. I also tried table value function. I could not make it work either! – Ashkan Feb 10 '13 at 17:45
  • @AshkanAleAli I think the only way is to use this inside a stored procedure, may be you can use it inside a table value function I am not sure for the later. – Mahmoud Gamal Feb 10 '13 at 18:14