Database Table transpose

Problem

This problem occurs at work.

If a student has multiple scores and each score is recorded in the table, the score of all the students must be printed into one row after the query, that is, multiple records in the table are merged into one record.

In sales management, the sales number of each month is represented as a record in the Table. During statistics, you need to output the sales statistics for all months in one row according to the product.

Of course, for some relatively rigid people, it can be said that I just want to record multiple fields, but this scalability is very poor. If the subject changes, do you have to change the table structure?

Prepare data

In the following example, we create the following table.

create table Orders(    ProductID  int,    OrderMonth int,    SubTotal    money)

 

Each row in the table indicates the monthly sales status of a product.

Then insert some data.

insert into Orders ( ProductID, OrderMonth, SubTotal )select 1, 5,  100.00  union allselect 1, 6,  100.00  union allselect 2, 5,  200.00  union allselect 2, 6,  200.00  union allselect 2, 7,  300.00  union allselect 3, 5,  400.00  union allselect 3, 5,  400.00  

The data in the table is as follows.

Now, we need to count the monthly sales of each product.

Method in SQL server2005

Use the token provided in SQL server2005 for transpose.

Select productid, [5] As May, [6] As June, [7] As July fromorders orders (sum (orders. subtotal) for orders. ordermonth in ([5], [6], [7]) as PVT order by productid;

 

Orders orders indicates the transpose operation on the table orders, which is called the input table.

Orders. ordermonth is called a pivot column (pivot_column), for orders. ordermonth in ([5], [6], [7]) indicates grouping and transposing for the months where ordermonth is 5, 6, and 7 in the table.

Sum (orders. Subtotal) indicates grouping and summation of subtotal in each group. The subtotal column is called a value column.

Select productid, [5] As May, [6] As June, [7] As July statement productid and ordermonth are combined to complete the grouping, followed by [5] As May, [6] As June, [7] As July correspond to for orders respectively. [5], [6], [7] In ordermonth in ([5], [6], [7]).

Follow these steps:

1. Group summation. Group summation is performed based on productid and ordermonth.

SELECT ProductID, OrderMonth, SUM (Orders.SubTotal) AS SumSubTotalFROM OrdersGROUP BY ProductID,OrderMonth;

 

The result is as follows:

2. based on the values 5, 6, and 7 specified by for ordermonth in, create a column named 5, 6, and 7 in the result set, then, the values in the ordermonth column are extracted from the preceding results and placed in columns 5, 6, and 7 respectively. The alias of the result set is PVT (see as PVT in the statement ). The content of the result set is as follows.

3. finally, according to the select productid, [5] As May, [6] As June, [7] As July from designation, retrieve data from the alias PVT result set, in the final result set, the columns named 5, 6, and 7 are renamed as May, June, and July respectively. Here, we need to note the meaning of from, which indicates to retrieve data from the PVT result set obtained by the operator of the orders relation, rather than from the sales. Orders.

The syntax of explain is as follows:

Select <Non-pivot column>, [First pivot column] As <column Name>, [second pivot column] As <column Name> ,... [last pivot column] As <column Name>, from (<SELECT query for generated data>) as <alias for Source Query> alias (<aggregate function> (<column to be aggregated>) for [<column containing the value of the column title>] In ([first pivot column], [second pivot column],... [last pivot column]) as <Pivot table alias> <optional order by clause>;

 

Note that if the aggregate function and aggregate function are used together, null values in the value column are not considered during aggregation calculation.

Method in SQL Server 2000

Before SQL Server 2005, The Case clause is usually used for processing.

Select productid, sum (case when ordermonth = 5 then subtotal end) as May, sum (case when ordermonth = 6 then subtotal end) as June, sum (case when ordermonth = 7 then subtotal end) as July from ordersgroup by productid

 

Note:

When I was using the Failover service today, a strange thing occurred, and the switch failed!

The table structure is as follows:

Create Table tbl_marks (markid int identity (), -- score keyword Sid int, -- Student id cid int, -- course ID mark int, -- score)

The data in the table is:

insert into tbl_marks ( sid, cid, mark ) values ( 1, 1, 100 );insert into tbl_marks ( sid, cid, mark ) values ( 1, 2, 90 );insert into tbl_marks ( sid, cid, mark ) values ( 1, 3, 95 );insert into tbl_marks ( sid, cid, mark ) values ( 2, 1, 60 );insert into tbl_marks ( sid, cid, mark ) values ( 2, 2, 61 );insert into tbl_marks ( sid, cid, mark ) values ( 2, 3, 99 );

The transpose statement is as follows:

select  sid, [1] as [database], [2] as [CSharp], [3] as [Xml]from   tbl_marks pivot(    sum( mark )    for cid in ( [1], [2], [3] )) as pvt

 

Note: The alias provided by the final as PVT must be available. Although there is no actual use, sqlserver 2005 must be provided.

The result is as follows:

1   100 NULL    NULL1   NULL    90  NULL1   NULL    NULL    952   60  NULL    NULL2   NULL    61  NULL2   NULL    NULL    99

 

There should be only two rows of results, which are actually six rows and are clearly not grouped!

After analysis, it is found that the grouping is based on implicit in grouping. Grouping groups all fields in the table except the group function compute column and the transposed column, in the above case, the group function evaluates the mark and transposes the CID of the course column. In the table, there are actually four columns, leaving the Markit and Sid columns, markit is a self-increasing ID column with different rows. In this way, the actual grouping is not performed on the SID field, leading to incorrect transpose results.

The solution is to restrict the columns in the table to three columns and exclude the mark column Markit. This problem can be easily solved through subqueries.

select  sid, [1] as [database], [2] as [CSharp], [3] as [Xml]from  ( select sid, cid, mark from  tbl_marks) t pivot(    sum( mark )    for cid in ( [1], [2], [3] )) as pvt

 

Note that a subquery in sqlserver requires an alias, although it is useless.

If case is used, the above transpose can be written in this way.

Select [Sid], max (case when cid = 1 then Mark end) as [database], min (case when cid = 2 then Mark end) as CSHARP, -- only sum the score of the course number 2. In fact, the score of the course number 2 only appears once, in fact, the returned result is the course 2 score AVG (case when cid = 3 then Mark end) as XML, sum (Mark) as [total score] From tbl_marks group by [Sid]

 

In this example, the group function can use not only sum, but also Max, Min, and AVG. Do you know why?

　　

　

References

For detailed case use instructions, refer to the case when syntax in the SQL statement of Qian tu without beam.

Http://www.cnblogs.com/qiantuwuliang/archive/2009/06/03/1495770.html

This article mainly references the following articles:

Zhang hongju's article: converting the rows and columns of a table in SQL Server 2005

Http://blog.csdn.net/zhanghongju/archive/2006/06/02/769445.aspx

Msdn: use logging and unlogging

Http://technet.microsoft.com/zh-cn/library/ms177410.aspx

Sman sky: The implementation of table data transpose in SQL Server 2000 and SQL Server 2005

Http://www.cnblogs.com/huangbaixun/archive/2008/07/26/1252002.html