How to pivot a table, without the Pivot/Cube command in MS SQL Server
Summary
- SQL Server 2005, DB2v9.x and Oracle 10g have the built-in command PIVOT to transform rows into columns.
- Older versions and other databases do not have that capability.
- We can emulate them with the select command.
select name,
case workdept
when 'A00' then salary
else null
end as "Dept A00",
case workdept
when 'C01' then salary
else null
end as "Dept C01"
from emps;
- Pivot/cube is a dynamic statement, you do not need to know all the column ahead of time.
- With this method, you will need to know all the columns ahead of time.
Data used
select empno, firstnme, midinit as mid, lastname, workdept as dept, job, salary from emps; empno firstnme mid lastname dept job salary ------ ------------ ---- --------------- ---- -------- --------------------------------------- 000010 CHRISTINE I HAAS A00 PRES 52750.00 000020 MICHAEL L THOMPSON B01 MANAGER 41250.00 000030 SALLY A KWAN C01 MANAGER 38250.00 000050 JOHN B GEYER E01 MANAGER 40175.00 000060 IRVING F STERN D11 MANAGER 32250.00 000070 EVA D PULASKI D21 MANAGER 36170.00 000090 EILEEN W HENDERSON E11 MANAGER 29750.00 000100 THEODORE Q SPENSER E21 MANAGER 26150.00 000110 VINCENZO G LUCCHESSI A00 SALESREP 46500.00 000120 SEAN O CONNELL A00 CLERK 29250.00 000130 DOLORES M QUINTANA C01 ANALYST 23800.00 000140 HEATHER A NICHOLLS C01 ANALYST 28420.00 000150 BRUCE ADAMSON D11 DESIGNER 25280.00 000160 ELIZABETH R PIANKA D11 DESIGNER 22250.00 000170 MASATOSHI J YOSHIMURA D11 DESIGNER 24680.00 000180 MARILYN S SCOUTTEN D11 DESIGNER 21340.00 000190 JAMES H WALKER D11 DESIGNER 20450.00 000200 DAVID BROWN D11 DESIGNER 27740.00 000210 WILLIAM T JONES D11 DESIGNER 18270.00 000220 JENNIFER K LUTZ D11 DESIGNER 29840.00 000230 JAMES J JEFFERSON D21 CLERK 22180.00 000240 SALVATORE M MARINO D21 CLERK 28760.00 000250 DANIEL S SMITH D21 CLERK 19180.00 000260 SYBIL P JOHNSON D21 CLERK 17250.00 000270 MARIA L PEREZ D21 CLERK 27380.00 000280 ETHEL R SCHNEIDER E11 OPERATOR 26250.00 000290 JOHN R PARKER E11 OPERATOR 15340.00 000300 PHILIP X SMITH E11 OPERATOR 17750.00 000310 MAUDE F SETRIGHT E11 OPERATOR 15900.00 000320 RAMLAL V MEHTA E21 FIELDREP 19950.00 000330 WING LEE E21 FIELDREP 25370.00 000340 JASON R GOUNOT E21 FIELDREP 23840.00 (32 row(s) affected)
- The replacement for the pivot or the cube statement is: multiple case statements.
- There is one case statement per column required, with a null/blank value if it is not the correct column.
select firstnme + ' ' + midinit + '. ' + lastname as "Employee",
case workdept
when 'A00' then salary
else null
end as "Dept A00",
case workdept
when 'C01' then salary
else null
end as "Dept C01",
case workdept
when 'D11' then salary
else null
end as "Dept D11",
case workdept
when 'D21' then salary
else null
end as "Dept D21",
case workdept
when 'E01' then salary
else null
end as "Dept E01",
case workdept
when 'E11' then salary
else null
end as "Dept E11",
case workdept
when 'E21' then salary
else null
end as "Dept E21"
from emps
ORDER BY lastname + firstnme;
Employee Dept A00 Dept C01 Dept D11 Dept D21 Dept E01 Dept E11 Dept E21
------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
BRUCE . ADAMSON NULL NULL 25280.00 NULL NULL NULL NULL
DAVID . BROWN NULL NULL 27740.00 NULL NULL NULL NULL
JOHN B. GEYER NULL NULL NULL NULL 40175.00 NULL NULL
JASON R. GOUNOT NULL NULL NULL NULL NULL NULL 23840.00
CHRISTINE I. HAAS 52750.00 NULL NULL NULL NULL NULL NULL
EILEEN W. HENDERSON NULL NULL NULL NULL NULL 29750.00 NULL
JAMES J. JEFFERSON NULL NULL NULL 22180.00 NULL NULL NULL
SYBIL P. JOHNSON NULL NULL NULL 17250.00 NULL NULL NULL
WILLIAM T. JONES NULL NULL 18270.00 NULL NULL NULL NULL
SALLY A. KWAN NULL 38250.00 NULL NULL NULL NULL NULL
WING . LEE NULL NULL NULL NULL NULL NULL 25370.00
VINCENZO G. LUCCHESSI 46500.00 NULL NULL NULL NULL NULL NULL
JENNIFER K. LUTZ NULL NULL 29840.00 NULL NULL NULL NULL
SALVATORE M. MARINO NULL NULL NULL 28760.00 NULL NULL NULL
RAMLAL V. MEHTA NULL NULL NULL NULL NULL NULL 19950.00
HEATHER A. NICHOLLS NULL 28420.00 NULL NULL NULL NULL NULL
SEAN . O CONNELL 29250.00 NULL NULL NULL NULL NULL NULL
JOHN R. PARKER NULL NULL NULL NULL NULL 15340.00 NULL
MARIA L. PEREZ NULL NULL NULL 27380.00 NULL NULL NULL
ELIZABETH R. PIANKA NULL NULL 22250.00 NULL NULL NULL NULL
EVA D. PULASKI NULL NULL NULL 36170.00 NULL NULL NULL
DOLORES M. QUINTANA NULL 23800.00 NULL NULL NULL NULL NULL
ETHEL R. SCHNEIDER NULL NULL NULL NULL NULL 26250.00 NULL
MARILYN S. SCOUTTEN NULL NULL 21340.00 NULL NULL NULL NULL
MAUDE F. SETRIGHT NULL NULL NULL NULL NULL 15900.00 NULL
DANIEL S. SMITH NULL NULL NULL 19180.00 NULL NULL NULL
PHILIP X. SMITH NULL NULL NULL NULL NULL 17750.00 NULL
THEODORE Q. SPENSER NULL NULL NULL NULL NULL NULL 26150.00
IRVING F. STERN NULL NULL 32250.00 NULL NULL NULL NULL
MICHAEL L. THOMPSON NULL NULL NULL NULL NULL NULL NULL
JAMES H. WALKER NULL NULL 20450.00 NULL NULL NULL NULL
MASATOSHI J. YOSHIMURA NULL NULL 24680.00 NULL NULL NULL NULL
(32 row(s) affected)
- Not very readable.
- This is Microsoft implementation of SQL Server to display the nulls
Improved, easier to read in MS SQL Server
- MS SQL server makes it difficult to read the table by displaying the NULL
- Other databases display a blank instead of the word NULL
- The simplest is to convert the salary to a string.
cast(salary as varchar(12))
select firstnme + ' ' + midinit + '. ' + lastname as "Employee",
case workdept
when 'A00' then cast(salary as varchar(12))
else ' '
end as "Dept A00",
case workdept
when 'C01' then cast(salary as varchar(12))
else ' '
end as "Dept C01",
case workdept
when 'D11' then cast(salary as varchar(12))
else ' '
end as "Dept D11",
case workdept
when 'D21' then cast(salary as varchar(12))
else ' '
end as "Dept D21",
case workdept
when 'E01' then cast(salary as varchar(12))
else ' '
end as "Dept E01",
case workdept
when 'E11' then cast(salary as varchar(12))
else ' '
end as "Dept E11",
case workdept
when 'E21' then cast(salary as varchar(12))
else ' '
end as "Dept E21"
from emps
ORDER BY lastname + firstnme;
Employee Dept A00 Dept C01 Dept D11 Dept D21 Dept E01 Dept E11 Dept E21
------------------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------
BRUCE . ADAMSON 25280.00
DAVID . BROWN 27740.00
JOHN B. GEYER 40175.00
JASON R. GOUNOT 23840.00
CHRISTINE I. HAAS 52750.00
EILEEN W. HENDERSON 29750.00
JAMES J. JEFFERSON 22180.00
SYBIL P. JOHNSON 17250.00
WILLIAM T. JONES 18270.00
SALLY A. KWAN 38250.00
WING . LEE 25370.00
VINCENZO G. LUCCHESSI 46500.00
JENNIFER K. LUTZ 29840.00
SALVATORE M. MARINO 28760.00
RAMLAL V. MEHTA 19950.00
HEATHER A. NICHOLLS 28420.00
SEAN . O CONNELL 29250.00
JOHN R. PARKER 15340.00
MARIA L. PEREZ 27380.00
ELIZABETH R. PIANKA 22250.00
EVA D. PULASKI 36170.00
DOLORES M. QUINTANA 23800.00
ETHEL R. SCHNEIDER 26250.00
MARILYN S. SCOUTTEN 21340.00
MAUDE F. SETRIGHT 15900.00
DANIEL S. SMITH 19180.00
PHILIP X. SMITH 17750.00
THEODORE Q. SPENSER 26150.00
IRVING F. STERN 32250.00
MICHAEL L. THOMPSON
JAMES H. WALKER 20450.00
MASATOSHI J. YOSHIMURA 24680.00
(32 row(s) affected)