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.
You can also create a dynamic pivot query, which uses a dynamic columns for pivot table, means you do not need to pass hard coded column names that you want to display in your pivot table. Dynamic pivot query will fetch a value for column names from table and creates a dynamic columns name list for pivot table.
A dynamic range will automatically expand or contract, if new columns or rows of data are added, or data is removed. You can base a pivot table on the dynamic range. Then, when you refresh the pivot table, it will include all of the data in the range, even if new rows or columns have been added.
You gotta change the name of columns for next Pivot Statement. You can use aggregate of pv3 to sum and group by the column you need. The key point here is that you create new category values by appending 1 or 2 to the end. Without doing this, the pivot query won't work properly.
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);
This will give you something like this:
| OBJECT_ID | PROPERTY1 | PROPERTY2 | PROPERTY3 | PROPERTY4 |
-------------------------------------------------------------
| 1 | ee | fd | fdf | ewre |
| 2 | dsd | sss | dfew | dff |
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With