Nov 11

How to make a Cross Tab SQL script

Executive reports are usually done in a cross-tab manner. The problem with developers is how to do this out of the SQL code directly without any help from Report wizards.

Usually, when you do a select statement, it will just produce all the records row after row. just like this:

SELECT Job, LengthStay, Salary FROM Employees

Results:

1. Clerk , 10 , 12000

2. Clerk, 15, 14000

3. Mgr , 20, 15000

4. Sales, 15, 11000

To make it a cross-tab report , just simply use a CASE statement:

SELECT  Job,

SUM(CASE WHEN Lengthstay = 10 THEN Salary ELSE 0 END ) as 10Years,

SUM(CASE WHEN Lengthstay =  15 THEN Salary ELSE 0 END ) as 15Years,

SUM(CASE WHEN Lengthstay = 20 THEN Salary ELSE 0 END ) as 20Years

FROM Employees

GROUP BY Job


sql crosstab programming
  1. sonborj posted this
My name is Sanborj.
and this is my life.