Home >Database >Mysql Tutorial >How to Transpose Data in SQL Server Using the PIVOT Operator?

How to Transpose Data in SQL Server Using the PIVOT Operator?

DDD
DDDOriginal
2024-12-25 04:58:20398browse

How to Transpose Data in SQL Server Using the PIVOT Operator?

How to Transpose SQL Server Data Using PIVOT

The PIVOT operator in SQL Server allows you to convert rows into columns, transforming data from a vertically oriented form to a horizontally oriented form. This can be useful when seeking to summarize data based on multiple attributes.

Example Data and Desired Output

Consider the following starting dataset:

SELECT Name1, Name2, Value FROM mytable

Name1 | Name2 | Value
-------+-------+------
A      | P1     | 1
A      | P2     | 1
A      | P3     | 2
B      | P1     | 3
B      | P2     | 1
B      | P4     | 1

The desired output is to pivot the data so that the PIVOT operator converts the Name1 column values into column headers, and the values from the Value column are summarized by the Name2 column.

Transposing Data with PIVOT in SQL Server 2005

For SQL Server 2005, the PIVOT operator can be used in the following manner:

DECLARE @cols VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)

SELECT  @cols = STUFF(( SELECT distinct  ',' + QuoteName([Name1])
                        FROM myTable FOR XML PATH('') ), 1, 1, '') 


SET @sqlquery = 'SELECT * FROM
      (SELECT Name2, Name1, Value
       FROM myTable ) base
       PIVOT (Sum(Value) FOR [Name1]
       IN (' + @cols + ')) AS finalpivot'

EXECUTE ( @sqlquery )

This query dynamically generates a PIVOT query based on the distinct values in the Name1 column, assembling the column headers and summarizing the data accordingly. Ultimately, the result is the transposed data in the desired format:

P1      P2      P3      P4
-------+-------+-------+-------
1       1       2       NULL
3       1       NULL    1

The above is the detailed content of How to Transpose Data in SQL Server Using the PIVOT Operator?. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn