Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to convert the rows and rows of PIVOT in SQL". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to convert rows and rows of PIVOT in SQL".

PIVOT rotates the table-valued expression by converting a unique value in one column of the expression to multiple columns in the output and, if necessary, aggregates any remaining column values needed in the final output. UNPIVOT does the opposite of PIVOT, converting the columns of a table-valued expression to column values.

To put it simply, PIVOT is a row transfer, and UNPIVOT is a column.

1. PIVOT instance

1. Build a table

Create a sales table where the year field represents the year, the quarter field represents the quarter, and the amount field represents sales. The quarter field uses Q1, Q2, Q3, and Q4 to represent the first, second, third and fourth quarters, respectively.

CREATE TABLE SalesByQuarter (year INT,-- year quarter CHAR (2),-- quarterly amount MONEY-- Total)

two。 Fill in the table data

Use the following procedure to fill in the table data.

SET NOCOUNT ON DECLARE @ index INT DECLARE @ q INT SET @ index = 0 DECLARE @ year INT while (@ index

< 30) BEGIN SET @year = 2005 + (@index % 4) SET @q = (CAST((RAND() * 500) AS INT) % 4) + 1 INSERT INTO SalesByQuarter VALUES (@year, 'Q' + CAST(@q AS CHAR(1)), RAND() * 10000.00) SET @index = @index + 1 3、如果我们要比较每年中各季度的销售状况，要怎么办呢？有以下两种方法： （1）、使用传统Select的CASE语句查询 在SQL Server以前的版本里，将行级数据转换为列级数据就要用到一系列CASE语句和聚合查询。虽然这种方式让开发人员具有了对所返回数据进行高度控制的能力，但是编写出这些查询是一件很麻烦的事情。 SELECT year as 年份 , sum (case when quarter = 'Q1' then amount else 0 end) 一季度 , sum (case when quarter = 'Q2' then amount else 0 end) 二季度 , sum (case when quarter = 'Q3' then amount else 0 end) 三季度 , sum (case when quarter = 'Q4' then amount else 0 end) 四季度 FROM SalesByQuarter GROUP BY year ORDER BY year DESC 得到的结果如下：

(2) use PIVOT

Because SQL Server 2005 has a new PIVOT operator, CASE statements and GROUP BY statements are no longer needed. Each PIVOT query involves some type of aggregation, so you can ignore the GROUP BY statement. The PIVOT operator allows us to use case statement queries to achieve the same function, but you can do it in less code and look more beautiful.

SELECT year as year, Q1 as first quarter, Q2 as second quarter, Q3 as third quarter, Q4 as fourth quarter FROM SalesByQuarter PIVOT (SUM (amount) FOR quarter IN (Q1, Q2, Q3, Q4)) AS P ORDER BY YEAR DESC

The results are as follows:

Second, introduce the process of PIVOT in detail through the following example

SELECT [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday]-- here is the third step of PIVOT (selecting the columns of the result set after row conversion), where you can use "*" to select all columns, or you can select only certain columns (that is, certain days) FROM WEEK_INCOME-- here is the second step of PIVOT (preparing the original query results). Because PIVOT converts an original query result set, query a result set first) here it can be a select subquery, but specify an alias for the subquery, otherwise the syntax error PIVOT (SUM (INCOME) for [week] in ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])-- here is the first step of PIVOT It is also the core of the row-to-column operation. The aggregate function SUM indicates how you need to handle the values of the converted columns, whether they are sum, avg, min,max, and so on. For example, if there are two pieces of data in the week_income table and its week is "Monday", one of them has an income of 1000 and the other income is 500. then using sum here, the value of the "Monday" column after the row is of course 1500. The following for [week] in ([Monday], [Tuesday]...) For [week] means to convert the values of week columns into individual columns, that is, "change columns by value". But there may be a lot of values that need to be converted to columns, and we just want to convert a few of them into columns, so how do we get them? It's in in. For example, I only want to see the working day's income at the moment. In in, I only write "Monday" to "Friday" (note that in contains the value of the original week column, "change the column by value"). In general, the meaning of the sentence SUM (INCOME) for [week] in ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday]) is translated literally, that is, convert the column [week] to "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday" and "Sunday" respectively. The values of these columns are the sum of income.) The TBL-- alias must be written.

III. UNPIVOT

Obviously, the prefix UN indicates that it does the opposite of PIVOT, that is, column wrapping. The UNPIVOT operation involves the following three logical processing phases.

1, make a copy

2, extract elements

3. Delete the line with NULL

UNPIVOT instance

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int, Emp3 int, Emp4 int, Emp5 int); GOINSERT INTO pvt VALUES (1, 4, 3, 5, 5, 4); INSERT INTO pvt VALUES (2, 4, 1, 5, 5, 5, 5, 5); INSERT INTO pvt VALUES (3, 4, 3, 5, 4, 4); INSERT INTO pvt VALUES (4, 4, 2, 5, 5, 5 and 4); INSERT INTO pvt VALUES (5, 5, 5, 5, 5, 5, 5) GO--Unpivot the table.SELECT VendorID, Employee, OrdersFROM (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) pUNPIVOT (Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5)) AS unpvt;GO

Analysis of the above UNPIVOT example

The input to UNPIVOT is the left table expression P, the first step is to make multiple copies of the rows in P, and a copy is made for each column that appears in UNPIVOT. Because the IN clause here has five column names, five copies are made for each source row. A new column will be added to the resulting virtual table to hold the name of the source column in string format (between for and IN, the above example is Employee). The second step is to extract the row corresponding to the column name from the source column according to the value in the new column. The third step is to delete the row with the result column value of null to complete the query.

Thank you for reading, the above is the content of "how to convert the ranks of PIVOT in SQL". After the study of this article, I believe you have a deeper understanding of how to convert the ranks of PIVOT in SQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.