Quota and unregister

Syntax:

Select <Non-pivot columns>,

[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 <Source Query alias>

Bytes

(

<Aggregate function> (<column to be aggregated>)

For

[<Column containing the value of the column title>]

In ([the first pivot column], [the second pivot column],

... [Last pivot column])

) As <Pivot table alias>

<Optional order by clause>;

Code:

Create Table tb2 (serial_no int, testname varchar (10 ))

Insert into tb2
Select 1, 'normal' Union all
Select 1, 'normal' Union all
Select 3, 'project' Union all
Select 2, 'project'

Select * from (select * From tb2) TMP evaluate (count (serial_no) for testname in (normal, Project) TD

I think:

1. Execute group by for columns other than distinct first. Select S1, S2, s3... from tb2 group by S1, S2, s3...

Select testname, count (serial_no) from tb2 group by testname. If no other columns exist, the preceding execution result is displayed.

2. Put in the for in (normal, Project) in the column, and the aggregate function value is displayed as a value.

Unregister

On the contrary, convert columns into rows.

Create Table PVT (vendorid int, emp1 int, emp2 int,
Emp3 int, emp4 int, emp5 INT );
Go
Insert into PVT values );
Insert into PVT values );
Insert into PVT values (3, 4, 3, 5, 4 );
Insert into PVT values );
Insert into PVT values );
Go
-- Unregister the table.
Select vendorid, employee, orders
From
(Select vendorid, emp1, emp2, emp3, emp4, emp5
From PVT) P
Unregister
(Orders for employee in
(Emp1, emp2, emp3, emp4, emp5)
) As unpvt;
Go
Select * From PVT

Unregister
(Orders for employee in
(Emp1, emp2, emp3, emp4, emp5)
)

Orders indicates the value, and employee indicates converting the column into a row ..

Select wfcode,
[Security Event audit], [Audit Event notice], [process end], [Municipal Audit class audit], [event notice], [business office audit]
From
(Select * From wfinstance) as P
Bytes
(
Count (currentstepname)
For currentstepname in ([Security Event audit], [Audit Event notice], [process end], [Municipal Audit class audit], [event notice], [business office audit])
)
As PVT

Reference

Http://www.cnblogs.com/kerrycode/archive/2010/07/28/1786547.html

Http://www.cnblogs.com/chinabc/archive/2009/12/15/1624913.html

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

Http://www.cnblogs.com/emanlee/archive/2009/08/14/1546353.html