How to pivot a summary into one row with MSSQL

Often we need to pivot data, and Microsoft SQL Server starting with SQL Server 2005, provides the pivot command.
Pivot is not portable, it does not apply to the MSSQL 7, MSSQL 2000 or some other brand of the SQL Servers.
Here is a generic way of doing the pivot.
The added advantage of doing the pivot manually, is that you can better understand it.
The manual pivot is done through a bunch of case statements.
The summary to a single row is done through the sum.

Applies to:

Microsoft SQL Server 7
Microsoft SQL Server 2000
Microsoft SQL Server 2005
Microsoft SQL Server 2008

Data used

USE sql911;
go
SELECT * FROM projects;
go

PROJNO PROJNAME                 DEPTNO RESPEMP PRSTAFF                                 PRSTDATE                PRENDATE                MAJPROJ
------ ------------------------ ------ ------- --------------------------------------- ----------------------- ----------------------- -------
AD3100 ADMIN SERVICES           D01    000010  6.50                                    2004-01-01 00:00:00.000 2004-02-01 00:00:00.000 NULL
AD3110 GENERAL ADMINSYSTEMS     D21    000070  6.00                                    2004-02-02 00:00:00.000 2004-03-03 00:00:00.000 NULL
AD3111 PAYROLL PROGRAMMING      D21    000230  2.00                                    2001-03-01 00:00:00.000 2001-03-15 00:00:00.000 NULL
AD3112 PERSONNEL PROGRAMMING    D21    000250  1.00                                    2001-04-01 00:00:00.000 2001-07-01 00:00:00.000 NULL
AD3113 ACCOUNT PROGRAMMING      D21    000270  2.00                                    1999-01-01 00:00:00.000 1999-12-01 00:00:00.000 NULL
IF1000 QUERY SERVICES           C01    000030  2.00                                    2006-02-01 00:00:00.000 2006-02-15 00:00:00.000 NULL
IF2000 USER EDUCATION           C01    000030  1.00                                    2006-03-01 00:00:00.000 2006-03-20 00:00:00.000 NULL
MA2100 WELDLINE AUTOMATION      D01    000010  12.00                                   2006-04-01 00:00:00.000 2006-06-01 00:00:00.000 NULL
MA2110 W L PROGRAMMING          D11    000060  9.00                                    2006-05-01 00:00:00.000 2007-02-01 00:00:00.000 NULL
MA2111 W L PROGRAM DESIGN       D11    000220  2.00                                    2007-01-01 00:00:00.000 2007-02-02 00:00:00.000 NULL
MA2112 W L ROBOT DESIGN         D11    000150  3.00                                    2007-02-01 00:00:00.000 2007-02-02 00:00:00.000 NULL
MA2113 W L PRODCONT PROGS       D11    000160  3.00                                    2007-03-01 00:00:00.000 2007-04-01 00:00:00.000 NULL
OP1000 OPERATION SUPPORT        E01    000050  6.00                                    2007-04-01 00:00:00.000 2007-08-01 00:00:00.000 NULL
OP1010 OPERATION                E11    000090  5.00                                    2007-05-01 00:00:00.000 2007-08-01 00:00:00.000 NULL
OP2000 GEN SYSTEM SSERVICES     E01    000050  5.00                                    2006-02-01 00:00:00.000 2006-05-03 00:00:00.000 NULL
OP2010 SYSTEMS SUPPORT          E21    000100  4.00                                    2007-02-16 00:00:00.000 2006-05-02 00:00:00.000 NULL
OP2011 SCP SYSTEMS SUPPORT      E21    000320  1.00                                    2006-04-01 00:00:00.000 2007-07-01 00:00:00.000 NULL
OP2012 APPLICATIONS SUPPORT     E21    000330  1.00                                    2007-04-01 00:00:00.000 2007-07-01 00:00:00.000 NULL
OP2013 DB/DCSUPPORT             E21    000340  1.00                                    2006-05-01 00:00:00.000 2006-07-01 00:00:00.000 NULL
PL2100 WELD LINE PLANNING       B01    000020  1.00                                    2005-01-01 00:00:00.000 2005-03-01 00:00:00.000 NULL

(20 row(s) affected)

Data summary

SELECT deptno, COUNT(deptno) AS 'Entries'
FROM projects
GROUP BY deptno;
go

deptno Entries
------ -----------
B01    1
C01    2
D01    2
D11    4
D21    4
E01    2
E11    1
E21    4

(8 row(s) affected)

Now we want to have this table as a one row, and each department as a column.

Pivot summary into a single row

SELECT SUM(CASE WHEN deptno = 'b01' THEN 1 ELSE 0 END) AS 'Dept B01',
       SUM(CASE WHEN deptno = 'c01' THEN 1 ELSE 0 END) AS 'Dept C01',
       SUM(CASE WHEN deptno = 'd01' THEN 1 ELSE 0 END) AS 'Dept D01',
       SUM(CASE WHEN deptno = 'd11' THEN 1 ELSE 0 END) AS 'Dept D11',
       SUM(CASE WHEN deptno = 'd21' THEN 1 ELSE 0 END) AS 'Dept D21',
       SUM(CASE WHEN deptno = 'e01' THEN 1 ELSE 0 END) AS 'Dept E01',
       SUM(CASE WHEN deptno = 'e11' THEN 1 ELSE 0 END) AS 'Dept E11',
       SUM(CASE WHEN deptno = 'e21' THEN 1 ELSE 0 END) AS 'Dept E21'
FROM projects;
go

Dept B01    Dept C01    Dept D01    Dept D11    Dept D21    Dept E01    Dept E11    Dept E21
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           2           2           4           4           2           1           4

(1 row(s) affected)

By using the sum-case statement for each column, you summarize it all to a single row.

SELECT (CASE WHEN deptno = 'b01' THEN 1 ELSE 0 END) AS 'Dept B01',
       (CASE WHEN deptno = 'c01' THEN 1 ELSE 0 END) AS 'Dept C01',
       (CASE WHEN deptno = 'd01' THEN 1 ELSE 0 END) AS 'Dept D01',
       (CASE WHEN deptno = 'd11' THEN 1 ELSE 0 END) AS 'Dept D11',
       (CASE WHEN deptno = 'd21' THEN 1 ELSE 0 END) AS 'Dept D21',
       (CASE WHEN deptno = 'e01' THEN 1 ELSE 0 END) AS 'Dept E01',
       (CASE WHEN deptno = 'e11' THEN 1 ELSE 0 END) AS 'Dept E11',
       (CASE WHEN deptno = 'e21' THEN 1 ELSE 0 END) AS 'Dept E21'
FROM projects;

Dept B01    Dept C01    Dept D01    Dept D11    Dept D21    Dept E01    Dept E11    Dept E21
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
0           0           1           0           0           0           0           0
0           0           0           0           1           0           0           0
0           0           0           0           1           0           0           0
0           0           0           0           1           0           0           0
0           0           0           0           1           0           0           0
0           1           0           0           0           0           0           0
0           1           0           0           0           0           0           0
0           0           1           0           0           0           0           0
0           0           0           1           0           0           0           0
0           0           0           1           0           0           0           0
0           0           0           1           0           0           0           0
0           0           0           1           0           0           0           0
0           0           0           0           0           1           0           0
0           0           0           0           0           0           1           0
0           0           0           0           0           1           0           0
0           0           0           0           0           0           0           1
0           0           0           0           0           0           0           1
0           0           0           0           0           0           0           1
0           0           0           0           0           0           0           1
1           0           0           0           0           0           0           0

(20 row(s) affected)

This method of pivoting data with sum of case statements is very inflexible.
You need to know, ahead of time [at programming], how many columns will be used.
The pivot command is dynamic, meaning that it will evaluate the number of columns at run-time.

SQLHacks for Microsoft SQL Server
Home page Administration Dates & Times Data Manipulation FAQs Optimization Summaries Site Map About us About us Copyrights Credits Disclaimers Guaranties Privacy Support policies Feedback	Summary How to pivot a summary into one row with MSSQL How to pivot a summary into one row with MSSQL Often we need to pivot data, and Microsoft SQL Server starting with SQL Server 2005, provides the pivot command. Pivot is not portable, it does not apply to the MSSQL 7, MSSQL 2000 or some other brand of the SQL Servers. Here is a generic way of doing the pivot. The added advantage of doing the pivot manually, is that you can better understand it. The manual pivot is done through a bunch of case statements. The summary to a single row is done through the sum. Applies to: Microsoft SQL Server 7 Microsoft SQL Server 2000 Microsoft SQL Server 2005 Microsoft SQL Server 2008 Data used USE sql911; go SELECT * FROM projects; go PROJNO PROJNAME DEPTNO RESPEMP PRSTAFF PRSTDATE PRENDATE MAJPROJ ------ ------------------------ ------ ------- --------------------------------------- ----------------------- ----------------------- ------- AD3100 ADMIN SERVICES D01 000010 6.50 2004-01-01 00:00:00.000 2004-02-01 00:00:00.000 NULL AD3110 GENERAL ADMINSYSTEMS D21 000070 6.00 2004-02-02 00:00:00.000 2004-03-03 00:00:00.000 NULL AD3111 PAYROLL PROGRAMMING D21 000230 2.00 2001-03-01 00:00:00.000 2001-03-15 00:00:00.000 NULL AD3112 PERSONNEL PROGRAMMING D21 000250 1.00 2001-04-01 00:00:00.000 2001-07-01 00:00:00.000 NULL AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1999-01-01 00:00:00.000 1999-12-01 00:00:00.000 NULL IF1000 QUERY SERVICES C01 000030 2.00 2006-02-01 00:00:00.000 2006-02-15 00:00:00.000 NULL IF2000 USER EDUCATION C01 000030 1.00 2006-03-01 00:00:00.000 2006-03-20 00:00:00.000 NULL MA2100 WELDLINE AUTOMATION D01 000010 12.00 2006-04-01 00:00:00.000 2006-06-01 00:00:00.000 NULL MA2110 W L PROGRAMMING D11 000060 9.00 2006-05-01 00:00:00.000 2007-02-01 00:00:00.000 NULL MA2111 W L PROGRAM DESIGN D11 000220 2.00 2007-01-01 00:00:00.000 2007-02-02 00:00:00.000 NULL MA2112 W L ROBOT DESIGN D11 000150 3.00 2007-02-01 00:00:00.000 2007-02-02 00:00:00.000 NULL MA2113 W L PRODCONT PROGS D11 000160 3.00 2007-03-01 00:00:00.000 2007-04-01 00:00:00.000 NULL OP1000 OPERATION SUPPORT E01 000050 6.00 2007-04-01 00:00:00.000 2007-08-01 00:00:00.000 NULL OP1010 OPERATION E11 000090 5.00 2007-05-01 00:00:00.000 2007-08-01 00:00:00.000 NULL OP2000 GEN SYSTEM SSERVICES E01 000050 5.00 2006-02-01 00:00:00.000 2006-05-03 00:00:00.000 NULL OP2010 SYSTEMS SUPPORT E21 000100 4.00 2007-02-16 00:00:00.000 2006-05-02 00:00:00.000 NULL OP2011 SCP SYSTEMS SUPPORT E21 000320 1.00 2006-04-01 00:00:00.000 2007-07-01 00:00:00.000 NULL OP2012 APPLICATIONS SUPPORT E21 000330 1.00 2007-04-01 00:00:00.000 2007-07-01 00:00:00.000 NULL OP2013 DB/DCSUPPORT E21 000340 1.00 2006-05-01 00:00:00.000 2006-07-01 00:00:00.000 NULL PL2100 WELD LINE PLANNING B01 000020 1.00 2005-01-01 00:00:00.000 2005-03-01 00:00:00.000 NULL (20 row(s) affected) Data summary SELECT deptno, COUNT(deptno) AS 'Entries' FROM projects GROUP BY deptno; go deptno Entries ------ ----------- B01 1 C01 2 D01 2 D11 4 D21 4 E01 2 E11 1 E21 4 (8 row(s) affected) Now we want to have this table as a one row, and each department as a column. Pivot summary into a single row SELECT SUM(CASE WHEN deptno = 'b01' THEN 1 ELSE 0 END) AS 'Dept B01', SUM(CASE WHEN deptno = 'c01' THEN 1 ELSE 0 END) AS 'Dept C01', SUM(CASE WHEN deptno = 'd01' THEN 1 ELSE 0 END) AS 'Dept D01', SUM(CASE WHEN deptno = 'd11' THEN 1 ELSE 0 END) AS 'Dept D11', SUM(CASE WHEN deptno = 'd21' THEN 1 ELSE 0 END) AS 'Dept D21', SUM(CASE WHEN deptno = 'e01' THEN 1 ELSE 0 END) AS 'Dept E01', SUM(CASE WHEN deptno = 'e11' THEN 1 ELSE 0 END) AS 'Dept E11', SUM(CASE WHEN deptno = 'e21' THEN 1 ELSE 0 END) AS 'Dept E21' FROM projects; go Dept B01 Dept C01 Dept D01 Dept D11 Dept D21 Dept E01 Dept E11 Dept E21 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 1 2 2 4 4 2 1 4 (1 row(s) affected) By using the sum-case statement for each column, you summarize it all to a single row. SELECT (CASE WHEN deptno = 'b01' THEN 1 ELSE 0 END) AS 'Dept B01', (CASE WHEN deptno = 'c01' THEN 1 ELSE 0 END) AS 'Dept C01', (CASE WHEN deptno = 'd01' THEN 1 ELSE 0 END) AS 'Dept D01', (CASE WHEN deptno = 'd11' THEN 1 ELSE 0 END) AS 'Dept D11', (CASE WHEN deptno = 'd21' THEN 1 ELSE 0 END) AS 'Dept D21', (CASE WHEN deptno = 'e01' THEN 1 ELSE 0 END) AS 'Dept E01', (CASE WHEN deptno = 'e11' THEN 1 ELSE 0 END) AS 'Dept E11', (CASE WHEN deptno = 'e21' THEN 1 ELSE 0 END) AS 'Dept E21' FROM projects; Dept B01 Dept C01 Dept D01 Dept D11 Dept D21 Dept E01 Dept E11 Dept E21 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 0 0 1 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 (20 row(s) affected) This method of pivoting data with sum of case statements is very inflexible. You need to know, ahead of time [at programming], how many columns will be used. The pivot command is dynamic, meaning that it will evaluate the number of columns at run-time.
All contents in SQLHacks.com are the property of db Networks Ltd and it's affiliates. All other trademarks not owned by db Networks Ltd or its affiliates that appear on the SQLHacks.com site are the property of their respective owners, especially: Windows^�, MS SQL^�, SQL Server^� which are the property of Microsoft Corp. DB2^� which is the property of IBM Systems. Oracle database^� which is the property of Oracle Corp.