How to Dynamically Pivot Multiple Columns in T-SQL?

Detailed explanation of T-SQL dynamic pivot table

This article explains how to convert a table with data stored in multiple columns into a pivot table format, where each value is displayed in its own column.

Suppose you have a table that contains project details for multiple projects, each with three different attributes: name, phone, and email. The goal is to reshape this data into a format where each item has its own row and attributes are listed as columns.

Dynamic pivot technology in T-SQL can effectively complete this conversion. The following steps outline the method:

1. Building dynamic SQL queries:

   • Dynamicly generate queries using the provided data.
   • Assign dynamic SQL query to variable (@query).

2. Execute dynamic SQL query:

   • Execute the @query variable, which will run a dynamic SQL query that performs pivot operations.

3. Dynamicly generate column list:

   • Use the STUFF function to concatenate the column names into a comma-separated list, named @cols.
   • This column list will be used as the PIVOT column specification.

4. Use the perspective function:

   • In the PIVOT clause, specify the column that will become the new column header and the value that will be pivoted into the new row.
   • Specify the 'MAX([Value])' aggregate function to retrieve the maximum value for each column name.

The T-SQL example provided illustrates this process. It demonstrates how to dynamically create a query, generate a column list, and ultimately pivot the data to achieve the desired results.

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