SQL2005 statement for row to column, column change

When making a report, it is often necessary to turn rows in the data table, or to make a career change, and if you do not know the method, you will find it very difficult to implement it through SQL statements. Here, I will use pivot and UNPIVOT to achieve seemingly complex functions. This feature is available in the sql2005 and above versions.

Reference MSDN:
You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression to another table. PIVOT rotates table-valued expressions by converting unique values in one column of an expression to multiple columns in the output, and performs aggregations if necessary for any remaining column values that are required in the final output. UNPIVOT performs a reverse operation with PIVOT, converting columns of table-valued expressions to column values, but in real-world applications, some data after aggregation is difficult to split. So, Unpivot is not the inverse process of pivot.

Pivot provides more syntax than a series of complex SELECT ... The syntax specified in the case statement is simpler and more readable.
Use SELECT ... Case statement for row-to-column method see: Resolving SQL Server Row-to-column issues

A simple example is as follows:

---------行转列
--建表
create table test(编号 int,姓名 varchar(20),季度 int,销售额 int) 
insert into test values(1,‘simon‘,1,1000)
insert into test values(1,‘simon‘,2,2000)
insert into test values(1,‘simon‘,3,3000)
insert into test values(1,‘simon‘,4,4000)
insert into test values(2,‘meme‘,1,5000)
insert into test values(2,‘meme‘,2,6000)
insert into test values(2,‘meme‘,3,7000)
insert into test values(2,‘meme‘,4,8000)

--执行普通查询
select * from test

--执行转换查询
select 编号,姓名,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度",
[5] as "随便1"
from
test
pivot
(
sum(销售额) 
for 季度 in
([1],[2],[3],[4],[5])
)
as pvt

--------Column line Change
--Build Tables
CREATE TABLE test2 (number int, name varchar (20), first quarter int, two quarter int, three quarter int, four quarter int)
INSERT INTO test2 values (1, ' Simon ', 1000,2000,4000,5000)
INSERT INTO test2 values (2, ' meme ', 3000,3500,4200,5500

-Execute a normal query
select * from Test2

--Perform conversion query
Select number, name, quarter, sales
from
test2
Unpivot
(
Sales
for quarter in
(first quarter, two quarter, three quarter, Four seasons)
)  
as Unpvt

Suggestion: If you want to know more clearly, please refer to: http://technet.microsoft.com/zh-cn/library/ms177410.aspx
Note: When you use PIVOT and UNPIVOT for a database that is upgraded to SQL Server 2005 or later, you must set the compatibility level of the database to 90 or higher.

Some initial installation of SQL Server 2005, the default compatibility level is "80", then we need to set the compatibility level, otherwise, pivot does not perform properly. I encountered this problem when I was using pivot.
The specific modifications are as follows:

To modify the compatibility level step
1. After connecting to the appropriate instance of the SQL Server database engine, in Object Explorer, click the server name to expand the server tree.
2. Expand Databases, and then select the user database, or expand system databases, and then select the system database, depending on the database.
3. Right-click the database, and then click Properties.
4. In the Database Properties dialog box, click Options, and you will see the Compatibility Level list box.
5. To change the compatibility level, select a different option from the list.
Available options include SQL Server 2000 (80), SQL Server 2005 (90), or SQL Server 2008 (100).
Please refer to the differences between the specific compatibility levels: http://technet.microsoft.com/zh-cn/library/bb510680.aspx

SQL2005 statement for row to column, column change