Implementation (aggregate function), SQLServer

Implementation (aggregate function), SQLServer

Sometimes you may need to convert rows to columns (that is, using the column value as the column name). I usually use the CASE END + aggregate function.

As follows:

Declare @ t table (StudentName nvarchar (20), Subject nvarchar (20), Score int) Insert into @ t (StudentName, Subject, Score) values ('student ', 'Chinese', 80); Insert into @ t (StudentName, Subject, Score) values ('student A', 'mat', 78); Insert into @ t (StudentName, subject, Score) values ('student A', 'English ', 92); Insert into @ t (StudentName, Subject, Score) values ('student B', 'China ', 89); Insert into @ t (StudentName, Subject, Score) values ('student B ', 'mat', 87); Insert into @ t (StudentName, Subject, Score) values ('student B ', 'English', 75); Insert into @ t (StudentName, Subject, Score) values ('student C', 'China', 92 ); insert into @ t (StudentName, Subject, Score) values ('student C', 'mat', 74); Insert into @ t (StudentName, Subject, Score) values ('student C', 'English ', 65); Insert into @ t (StudentName, Subject, Score) values ('student d', 'China', 79 ); insert into @ t (StudentName, Subject, Score) values ('student d', 'mat', 83); Insert into @ t (StudentName, Subject, Score) values ('student d', 'English ', 81); Insert into @ t (StudentName, Subject, Score) values ('student E', 'China', 73 ); insert into @ t (StudentName, Subject, Score) values ('student E', 'mat', 84); Insert into @ t (StudentName, Subject, Score) values ('student E', 'English ', 93); Insert into @ t (StudentName, Subject, Score) values ('student F', 'China', 79 ); insert into @ t (StudentName, Subject, Score) values ('student F', 'mat', 86); Insert into @ t (StudentName, Subject, Score) values ('student F', 'English ', 84); select StudentName, sum (case when Subject = n' then Score else 0 end) Chinese, sum (case when Subject = n' then Score else 0 end) Math, sum (case when Subject = n' then Score else 0 end) engilsh from @ t group by StudentName

Today we see a new way of writing, which can implement the same function (2005 is supported ).

The syntax of explain is:

Table_source

Aggregate (value_column) aggregate t_column for (columnlist ))

A little explanation:

Table_source:Is the table to be converted.Effect_column:Is the name of the column for Row-to-column conversion.Value_column:Is the value of the converted column. Columnlist is the column to be generated.

Similarly, in the above example, you can write the same result using the delimiter:

Select StudentName, [Chinese] Chinese, [mathematics] Math, [English] English from (select * from @ t) t11_( sum (Score) for Subject in ([Chinese], [English], [mathematics]) t2

The corresponding unrecord is the column to be transposed (the column name is used as the value ),

Unregister Syntax:

Table_source

Unregister (value_column ub1_t_column for (columnlist ))

The parameter has the same meaning as the callback parameter. Here, we can simply convert the original table to another one:

Select StudentName, Subject, Score from (select * from @ t) t1133 (sum (Score) for Subject in ([Chinese], [English], [mathematics]) t2ungoal (Score for Subject in ([Chinese], [English], [mathematics]) t3

The above is all the content in this article, and I hope it will help you learn how to implement.

Articles you may be interested in:

• SQL Server 2000 row-to-column conversion implementation (horizontal)
• SQL Server String Aggregate functions
• Sqlserver2005 row and column conversion Implementation Method
• Summary of SQL Server row-column Conversion
• How to convert Column records recorded in the database into rows under sqlserver
• SQL Server Implementation of Row-to-column Conversion
• SQL Server aggregate function algorithm optimization skills