Mega Code Archive
PIVOT and UNPIVOT Operators
<![CDATA[
4> create table department(
5> dept_name char(20) not null,
6> emp_cnt int not null,
7> budget float,
8> date_month datetime);
9> GO
1>
2> insert into department values('Research', 5, 50000, '01.01.2002');
3> insert into department values('Research', 10, 70000, '01.02.2002');
4> insert into department values('Research', 5, 65000, '01.07.2002');
5> insert into department values('Accounting', 5, 10000, '01.07.2002');
6> insert into department values('Accounting', 10, 40000, '01.02.2002');
7> insert into department values('Accounting', 6, 30000, '01.01.2002');
8> insert into department values('Accounting', 6, 40000, '01.02.2003');
9> insert into department values('Marketing', 6, 10000, '01.01.2003');
10> insert into department values('Marketing', 10, 40000, '01.02.2003');
11> insert into department values('Marketing', 3, 30000, '01.07.2003');
12> insert into department values('Marketing', 5, 40000, '01.01.2003');
13> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> select *, month(date_month) as month, year(date_month) as year
4> into department_pivot
5> from department
6> go
(11 rows affected)
1> SELECT year, [1] as January, [2] as February, [7] July FROM
2> (select budget, year, month from department_pivot) p2
3> PIVOT
4> (SUM(budget)
5> FOR month
6> IN ([1],[2],[7]))
7> AS P
8> GO
year January February July
----------- ------------------------ ------------------------ ------------------------
2002 265000 NULL NULL
2003 160000 NULL NULL
(2 rows affected)
1>
2> drop table department;
3> GO
1>
2>
3>
]]>