How to Pivot Rows to Columns in SQL Server Using the 'Pivot' Function?

Use SQL Server’s PIVOT function to convert rows into columns

When manipulating SQL Server data, it is often necessary to convert rows into columns to improve readability and analysis efficiency. The PIVOT function provides an efficient way to implement this conversion.

Suppose you have a temporary table that contains the store number, week number, and corresponding count. The goal is to convert this data into a pivot table format with store numbers arranged vertically and week numbers as column headers.

In order to accomplish this task using the PIVOT function, you can follow these steps:

1. Create a dynamic column list: If the number of weeks is unknown, you need to generate a list of all the different week numbers in the table. This can be done using subqueries and the STUFF function.
2. Create a pivot query: Then use the PIVOT function to aggregate the 'xCount' values ​​for each store and week. The FOR clause specifies the column names that will be generated from the list of week numbers.
3. Execute queries: You can use the EXECUTE statement to execute dynamic pivot queries.

If you know the week number in advance, you can use a hard-coded PIVOT query without dynamically generating the columns.

With these steps, raw data can be converted into a pivot table format, making it easier to analyze and present the results.

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