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

PIVOT and UNPIVOT Operators

   <source lang="sql">

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></source>