How to Perform Dynamic Pivoting in T-SQL?

Dynamic Pivot Table in T-SQL

In data processing, data reorganization is crucial. Pivot table technique is a common data conversion method that converts data from column format to row format. This article will delve into the concept of dynamic pivot tables in T-SQL and demonstrate its application in common scenarios.

Suppose there is a table with data arranged as follows:

<code>ItemID | ColumnName | Value
1      | name       | Peter
1      | phone      | 12345678
1      | email      | test@domain.com
2      | name       | John
2      | phone      | 87654321
2      | email      | example@gmail.com
3      | name       | Sarah
3      | phone      | 55667788
3      | email      | user@outlook.com</code>

Our goal is to pivot this data into a more readable format:

<code>ItemID | name  | phone    | email
1      | Peter | 12345678 | test@domain.com
2      | John  | 87654321 | example@gmail.com
3      | Sarah | 55667788 | user@outlook.com</code>

To achieve dynamic perspective, we use the following steps:

1. Prepare data:

   • Create a temporary table to store data.

2. Declare variables:

   • Declare variables to store dynamic column lists and SQL queries.

3. Build a dynamic column list:

   • Use nested SELECT statements to extract different column names.
   • Append values ​​as comma-separated strings, making sure they are enclosed in square brackets.

4. Create a dynamic pivot query:

   • Build PIVOT statements using dynamic column lists.
   • Use the MAX() function to aggregate any duplicate values ​​in the pivot output.

5. Execute query:

   • Execute dynamic pivot queries to retrieve transformed data.

By following these steps, we can successfully pivot data dynamically in T-SQL to present information more effectively.

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