SQL Server/T-SQL Tutorial/Analytical Functions/PIVOT

PIVOT and UNPIVOT Operators

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>