Use of PIVOT and Upivot (row to column)

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 the reverse operation with PIVOT, converting columns of table-valued expressions to column values.

In fact, Pivot is a row to the column, Unpivot is a career change.

The complete syntax for PIVOT is:

SELECT < non-perspective columns;

[First pivot column] as < column name;

[Second pivot column] as < column name;

...

[Last Pivot column] as < column name;

From

(< SELECT query > for generating data)

Aliases for as < source queries >

PIVOT

(

< aggregate functions > (< Columns to aggregate >)

For

[< The column that contains the value to be the column header;]

In ([First pivot column], [Second pivot column],

... [last pivot column])

) as < pivot table Aliases >

< optional ORDER by clause >;

The complete syntax of UNPIVOT is relatively simple:

SELECT < Other columns >,< virtual column Aliases >,< column value aliases >

UNPIVOT (

< column value aliases >

For < virtual column aliases >

In (< first real column >,< second real column; ...)

) as < table aliases >

We look at a simple pivot example, the project has the following requirements: According to the user input query month, statistics All equipment room this month's alarm number, the interface report requires the following format:

Equipment room	Alarm a number of times	Number of alarms B	Alarm C times
Xxx	10	1	2
ZZZ	1	0	5

For example: The database has the following tables and data:

--Engine room table CREATE TABLE t_devroom (roomid int identity, Roomname nvarchar (), constraint [Pk_devroom_roomid] Primary   Key Clustered (roomid), constraint [uq_devroom_roomname] Unique (roomname)) go--Alarm type CREATE TABLE t_alarmtype (TypeId int, TypeName nvarchar () NOT NULL, constraint [Pk_alarmtype_typeid] primary key clustered (TypeId), constraint [uq_alarm Type_typename] Unique (TypeName)) go--Alarm table CREATE TABLE t_alarm (alarmid int identity (), Roomid int not NULL, Alarmtyp The e int not NULL, ALARMDT datetime is NOT NULL, constraint [PK_ALARM_ALARMID] primary key clustered (Alarmid), constraint [ FK_ALARM_ROOMID] FOREIGN KEY (roomid) references T_devroom (roomid) on DELETE CASCADE, constraint [Fk_alarm_alarmtype] for Eign Key (Alarmtype) references T_alarmtype (TypeId) on DELETE cascade) Goinsert into t_devroom values (' Room a ') insert into T_  Devroom values (' Room B ') insert into t_devroom values (' Room C ') insert into t_alarmtype values (1, ' air conditioning alarm ') insert INTO T_alarmtype VALUES (2, ' smoke alarm ') insert Into T_alarmtype values (3, ' device alarms ') insert into t_alarm values ("2013-01-01") insert into t_alarm values (1, 1, ' 2013-01-02 ') insert into t_alarm values ("2013-01-02") insert into t_alarm values (1,3, ' 2013-01-03 ') insert INTO T_ Alarm values (1,3, ' 2013-01-04 ') insert into t_alarm values (2,2, ' 2013-01-01 ') insert into t_alarm values (2,2, ' 2013-01-02 ') INSERT into t_alarm values (2,3, ' 2013-01-02 ') insert to t_alarm values (2,3, ' 2013-01-03 ') insert into t_alarm values (2,3 , ' 2013-01-04 ')

With the above temporary data, we can check the number of alarms in all engine rooms in January 2013:

Select R.roomid,r.roomname,count (a.alarmtype) as Nums,t.typename from T_devroom as Rleft joins T_alarm as A on R.ROOMID=A.R Oomidleft join T_alarmtype as T on A.alarmtype=t.typeidwhere datepart (YEAR,A.ALARMDT) =2013 and datepart (month,a.alarmdt ) =1 or A.ALARMDT is nullgroup by R.roomid,r.roomname,t.typenameorder by Roomid

The results are as follows:

We're going to pivot this result set to meet our interface requirements, and we'll make this change according to the syntax format:

Select Roomid,roomname,       alarm_kt=isnull ([Air conditioning alarm],0),       alarm_yw=isnull ([Smoke alarm],0),       alarm_dv=isnull ([Device Alarm ],0) from (select R.roomid,r.roomname,t.typename,count (A.alarmtype) as Nums from T_devroom as Rleft join T_alarm as A on R.R Oomid=a.roomidleft join T_alarmtype as T on A.alarmtype=t.typeidwhere datepart (YEAR,A.ALARMDT) =2013 and datepart (month, A.ALARMDT) =1 or A.ALARMDT is nullgroup by R.roomid,r.roomname,t.typename) as Temppivot (   min (nums) for TypeName in ([Air conditioning Alarm],[Smoke Alarm],[Device alarm])) as Temp2order by Roomid

The query results are as follows:

As for UNPIVOT and pivot, let's look at an example from the Internet:

CREATE TABLE T_score (  name varchar (ten),  language int,  math int,  physical int) Goinsert into t_score values (' Zhang San ', 74,83,93 INSERT into T_score values (' John Doe ', 74,84,94) SELECT * FROM T_score
Select Name, course, score from T_score Unpivot (  score for Course in ([Language],[Math],[physics]) as Tgo

The results of the implementation are as follows:

Use of PIVOT and Upivot (row to column)