How to Create Dynamic Pivot Columns in SQL Server?

Detailed explanation of SQL Server dynamic pivot table columns

In SQL Server, Pivot Tables are used to reshape data by converting rows into columns. However, when column names are dynamic and not known beforehand, manually creating a pivot table becomes challenging. This article explores a solution to this problem by dynamically generating pivot columns from the table.

Suppose we have a Property table with the following columns:

<code>Id    Name</code>

We also have a PropertyObjects table that stores property values ​​for specific objects:

<code>Id    Object_Id    Property_Id    Value</code>

Our goal is to create a pivot table in which each column corresponds to an attribute defined in the Property table, and each column displays the value of the corresponding attribute and object ID.

In order to dynamically generate pivot columns, we can use the following steps:

1. Get all the different attribute names:

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, '');

This will create a comma separated string @cols containing the names of all properties.

2. Build a pivot query:

SET @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;';

This will build a dynamic SQL query @query that contains pivot logic.

3. Execute query:

EXEC sp_executesql @query;

Executing @query will return a pivot table with column names dynamically generated from the Property table.

With the above steps, we can effectively handle dynamic column names to create flexible and scalable pivot tables. It should be noted that this method relies on dynamic SQL, so be sure to ensure data security before use to avoid the risk of SQL injection.

The above is the detailed content of How to Create Dynamic Pivot Columns in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!