How Can I Dynamically Create Pivot Columns in SQL Server?

Dynamically Generating Pivot Columns in SQL Server

Creating pivot tables with dynamically generated columns in SQL Server requires dynamic SQL. This approach allows the pivot columns to adapt to changes in your source data.

Here's a breakdown of the process:

1. Building the Dynamic Column List: A variable (e.g., @cols) stores a comma-separated list of unique property names from your Property table. This list is generated using a query, often incorporating the STUFF function to handle string concatenation.

2. Constructing the Dynamic SQL Query: A dynamic SQL query (e.g., @query) is built using the PIVOT function. The FOR clause within the PIVOT statement references the @cols variable to define the dynamically generated column names.

3. Executing the Dynamic SQL Query: The sp_executesql stored procedure executes the constructed dynamic SQL query, using @query as the query parameter.

This method ensures your pivot table reflects any additions or changes in the Property table, providing a flexible and up-to-date view of property-object relationships.

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