Simplifying SQL Server query

Question

I'm trying to show the professor rank, the salary for that rank based on department number, and the total salary paid for all people of that rank. The i'm trying to format the output so that each column is a department number. This is what I have come up with so far.

SELECT

ProfRank.ProfRankName,



'$' + CAST(CAST( (COALESCE(Dept1.DeptSum, 0)) AS money) as varchar) as CSET,
'$' + CAST(CAST( (COALESCE(Dept2.DeptSum, 0)) AS money) as varchar) as HUM,
'$' + CAST(CAST( (COALESCE(Dept3.DeptSum, 0)) AS money) as varchar) as COM,
'$' + CAST(CAST( (COALESCE(Dept4.DeptSum, 0)) AS money) as varchar) as EET,    
'$' + CAST(CAST( ((COALESCE(Dept1.DeptSum, 0) + COALESCE(Dept2.DeptSum, 0) +   COALESCE(Dept3.DeptSum, 0) + COALESCE(Dept4.DeptSum, 0)) ) AS money) as varchar) as Total

FROM

    (SELECT ProfRank.ProfRankID, SUM(faculty.Salary) as DeptSum

    FROM faculty
    INNER JOIN dept ON faculty.DeptID = dept.DeptID
    INNER JOIN ProfRank ON faculty.Salary BETWEEN ProfRank.Minimumm AND ProfRank.Maximum
    WHERE dept.DeptID = 1
    GROUP BY ProfRankID
    ) as Dept1

     FULL OUTER JOIN
            (SELECT ProfRank.ProfRankID, SUM(faculty.Salary) as DeptSum
            FROM faculty
            INNER JOIN dept ON faculty.DeptID = dept.DeptID
            INNER JOIN ProfRank ON faculty.Salary BETWEEN ProfRank.Minimumm AND ProfRank.Maximum
            WHERE dept.DeptID = 2
            GROUP BY ProfRankID
            ) as Dept2

                    ON Dept1.ProfRankID = Dept2.ProfRankID

    FULL OUTER JOIN

            (SELECT ProfRank.ProfRankID, SUM(faculty.Salary) as DeptSum
            FROM faculty
            INNER JOIN dept ON faculty.DeptID = dept.DeptID
            INNER JOIN ProfRank ON faculty.Salary BETWEEN ProfRank.Minimumm AND ProfRank.Maximum
            WHERE dept.DeptID = 3
            GROUP BY ProfRankID
            ) as Dept3
                    ON Dept2.ProfRankID = Dept3.ProfRankID

    FULL OUTER JOIN
            (SELECT ProfRank.ProfRankID, SUM(faculty.Salary) as DeptSum
            FROM faculty
            INNER JOIN dept ON faculty.DeptID = dept.DeptID
            INNER JOIN ProfRank ON faculty.Salary BETWEEN ProfRank.Minimumm AND ProfRank.Maximum
            WHERE dept.DeptID = 4
            GROUP BY ProfRankID
            ) as Dept4
                    ON Dept3.ProfRankID = Dept4.ProfRankID

    INNER JOIN ProfRank
            ON ProfRank.ProfRankID = Dept1.ProfRankID
            OR ProfRank.ProfRankID = Dept2.ProfRankID
            OR ProfRank.ProfRankID = Dept3.ProfRankID
            OR ProfRank.ProfRankID = Dept4.ProfRankID

    ORDER BY ProfRank.ProfRankID

The output is:

ProfRankName                   CSET                            HUM                                 COM                             EET                             Total
------------------------------ ------------------------------- ------------------------------- --    ----------------------------- ------------------------------- -------------------------------
Instructor                     $0.00                           $6000.00                            $9000.00                        $0.00                           $15000.00
Assistant Professor            $38500.00                       $17000.00                           $0.00                           $0.00                           $55500.00
Associate Professor            $101000.00                      $26000.00                       $0.00                           $0.00                           $127000.00
Professor                      $105000.00                      $38000.00                       $39000.00                       $0.00                           $182000.00

(4 row(s) affected)

Is there any way to condense this code, and make it shorter and more efficient??

Here's the sql server file to create the database

Create table ProfRank
(
ProfRankID      int         constraint ProfRank_pk primary key,
ProfRankName    nvarchar(30),
Minimumm     decimal(8,2),
Maximum      decimal(8,2)
);
create table dept
(
DeptID      int     constraint Dept_pk primary key,
DeptName    nvarchar(50)
);
Create table major
(
MajorID     int     constraint Major_pk primary key,
MajorName   nvarchar(40)
);

create table faculty
(
    FacultyID   int     constraint Faculty_pk primary key,
    FirstName   nvarchar(15),
    LastName    nvarchar(15),
    Phone       nvarchar(10),
    Salary      decimal(8, 2),
    Stipend     decimal(7, 2),
    Hiredate    date,
    SupervisorID    int     constraint Faculty_Faculty_fk references faculty(facultyID),
    DeptID      int     constraint Faculty_dept_fk references dept(deptID)
);

create table student
(
    StudentID   int     constraint student_pk primary key,
    FirstName   nvarchar(15),
    LastName    nvarchar(15),
    Phone       nvarchar(10),
    AdvisorID   int     constraint student_Faculty_fk references faculty(facultyID)
);
create table student_major
(
        MajorID     int     constraint Student_major_major_fk references major(majorid),
        StudentID   int     constraint Student_major_student_fk references student(studentid),
    constraint  Student_majorpk primary key (majorid, studentid)
);
Insert into major values(1, 'Software Engineering Technology');
Insert into major values(2, 'Computer Engineering Technology');
Insert into major values(3, 'Applied Psychology');
Insert into major values(4, 'Communication Studies');

Insert into dept values(1, 'Computer Systems Engineering Technology')
Insert into dept values(2, 'Humanities and Social Science');
Insert into dept values(3, 'Communication');
Insert into dept values(4, 'Electronics Engineering Technology');

Insert into faculty values (1, 'Calvin', 'Caldwell', '885-1598', 33000.00, 3000.00, '15-sep-1986', Null, 1);
Insert into faculty values (2, 'Randy', 'Albert', '885-1596', 35000.00, 1500.00, '15-sep-1984', 1, 1);
Insert into faculty values (3, 'Ralph', 'Carestia', '885-1453', 37000.00, 1500.00,'5-Jan-1990', 1, 1);
Insert into faculty values (4, 'Todd', 'Breedlove', '885-1577',  23000.00, NULL,'15-sep-1999', 2,    1);
Insert into faculty values (5, 'Jamie', 'Zipay', '885-1543', 26000.00, NULL,'7-jan-2001', 3, 1);
Insert into faculty values (6, 'Phong', 'Nguyen', '885-1599', 25000.00, NULL,'15-sep-1999', 3, 1);
Insert into faculty values (7, 'Sherry', 'Yang', '885-1594', 27000.00, NULL,'15-sep-1997', 2, 1);
Insert into faculty values (8, 'Lynda', 'Baker', '885-1672', 38000.00, 3000.00, '15-sep-1989', Null, 2);
Insert into faculty values (9, 'Maria Lynn', 'Kessler', '885-1674', 26000.00, NULL, '15-sep-2003', 8, 2);
Insert into faculty values (10, 'John', 'Puckett', '885-1678', 39000.00, 3000.00, '15-sep-1989', Null, 3);
Insert into faculty values (11, 'Robin', 'Schwartz', '885-1398', 9000.00, NULL, '15-sep-1999', 10, 3);
Insert into faculty values (12, 'Jim', 'Long', '885-1580', 19500.00, NULL, '15-sep-2000', 2, 1);
Insert into faculty values (13, 'Tim', 'Stewart', '851-5160', 19000.00, NULL, '15-sep-2000', 2, 1);
Insert into faculty values (14, 'Leo', 'Dubray', '885-1492', 17000.00, NULL, '15-sep-2001', 8, 2);
Insert into faculty values (15, 'Michele', 'Malott', '885-1395', 6000.00, NULL, '15-sep-2005', 8, 2);

Insert into ProfRank values (1, 'Instructor', 00000.00, 10000.00);
Insert into ProfRank values (2, 'Assistant Professor', 10000.00, 20000.00);
Insert into ProfRank values (3, 'Associate Professor', 20000.00, 30000.00);
Insert into ProfRank values (4, 'Professor', 30000.00, 40000.00);

Insert into Student values (1, 'Paul', 'Scott', '882-1002', 1);
Insert into Student values (2, 'Chris', 'Ambiel', '883-1312', 13);
Insert into Student values (3, 'Jake', 'Brownson', '882-3424', 5);
Insert into Student values (4, 'Farhad', 'Shakiba', '884-1231', 5);
Insert into Student values (5, 'Allan', 'Snippen', '882-2342', 5);
Insert into Student values (6, 'Michael', 'Hart', '882-5464', 12);
Insert into Student values (7, 'Jonathan', 'Thibeau', '883-2342', 12);
Insert into Student values (8, 'Alberto', 'Martinez', '882-8796', 2);
Insert into Student values (9, 'Jeanie', 'King', '891-1234', 3);
Insert into Student values (10, 'Jason', 'Richards', '882-3456', 3);
Insert into Student values (11, 'Justin', 'Royse', '885-1111', 3);
Insert into Student values (12, 'Xinger', 'Yu', '883-2322', 2);
Insert into Student values (13, 'Storm', 'Dain', '885-3212', 13);
Insert into Student values (14, 'TJ', 'Atterberry', '883-1231', 12);
Insert into Student values (15, 'Roscoe', 'Casita', '883-1213', 4);
Insert into Student values (16, 'Shad', 'Cole', '882-3232', 4);
Insert into Student values (17, 'Luke', 'Goodale', '885-1002', 4);
Insert into Student values (18, 'Kyle', 'Spencer', '885-1012', 4);
Insert into Student values (19, 'Ed', 'Hudson', '882-1878', 7);
Insert into Student values (20, 'Scott', 'Ore', '883-9303', 7);
Insert into Student values (21, 'Ryan', 'McCarty', '884-1922', 6);
Insert into Student values (22, 'Devan', 'Stormont', '883-1999', 4);
Insert into Student values (23, 'Jeffrey', 'Bernt', '882-9999', 8);
Insert into Student values (24, 'Chris', 'Gheen', '883-3434', 8);
Insert into Student values (25, 'Cody', 'Zuschlag', '885-9654', 9);
Insert into Student values (26, 'Kevin', 'Wong', '883-1233', 9);
Insert into Student values (27, 'Andrew', 'Wilson', '885-2322', 10);
Insert into Student values (28, 'Jesse', 'Stafford', '882-2328', 10);
Insert into Student values (29, 'Kevin', 'Roberts', '882-1765', 14);
Insert into Student values (30, 'Tim', 'Clark', '882-8888', 2);

Insert into Student_major (studentid, majorid) values (1, 1);
Insert into Student_major (studentid, majorid) values (2, 1);
Insert into Student_major (studentid, majorid) values (3, 1);
Insert into Student_major (studentid, majorid) values (3, 2);
Insert into Student_major (studentid, majorid) values (4, 1);
Insert into Student_major (studentid, majorid) values (4, 2);
Insert into Student_major (studentid, majorid) values (5, 1);
Insert into Student_major (studentid, majorid) values (5, 2);
Insert into Student_major (studentid, majorid) values (6, 1);
Insert into Student_major (studentid, majorid) values (7, 1);
Insert into Student_major (studentid, majorid) values (8, 1);
Insert into Student_major (studentid, majorid) values (9, 2);
Insert into Student_major (studentid, majorid) values (10, 1);
Insert into Student_major (studentid, majorid) values (10, 2);
Insert into Student_major (studentid, majorid) values (11, 1);
Insert into Student_major (studentid, majorid) values (11, 2);
Insert into Student_major (studentid, majorid) values (12, 1);
Insert into Student_major (studentid, majorid) values (13, 1);
Insert into Student_major (studentid, majorid) values (14, 1);
Insert into Student_major (studentid, majorid) values (15, 1);
Insert into Student_major (studentid, majorid) values (16, 1);
Insert into Student_major (studentid, majorid) values (17, 1);
Insert into Student_major (studentid, majorid) values (18, 1);
Insert into Student_major (studentid, majorid) values (19, 1);
Insert into Student_major (studentid, majorid) values (20, 1);
Insert into Student_major (studentid, majorid) values (21, 2);
Insert into Student_major (studentid, majorid) values (22, 1);
Insert into Student_major (studentid, majorid) values (23, 3);
Insert into Student_major (studentid, majorid) values (24, 3);
Insert into Student_major (studentid, majorid) values (25, 3);
Insert into Student_major (studentid, majorid) values (26, 3);
Insert into Student_major (studentid, majorid) values (27, 4);
Insert into Student_major (studentid, majorid) values (28, 4);
Insert into Student_major (studentid, majorid) values (29, 4);
Insert into Student_major (studentid, majorid) values (30, 1);

Answer 1

SELECT r.ProfRankName,
    SUM(CASE d.DeptID WHEN 1 THEN f.Salary END) as DeptSum1,
    SUM(CASE d.DeptID WHEN 2 THEN f.Salary END) as DeptSum2,
    SUM(CASE d.DeptID WHEN 3 THEN f.Salary END) as DeptSum3,
    SUM(CASE d.DeptID WHEN 4 THEN f.Salary END) as DeptSum4,
    SUM(f.Salary) as Total
FROM faculty f
INNER JOIN dept d ON f.DeptID = d.DeptID
INNER JOIN ProfRank r ON f.Salary BETWEEN r.Minimumm AND r.Maximum
WHERE d.DeptID in (1,2,3,4)
GROUP BY r.ProfRankID, r.ProfRankName

To make the query more dynamic, in case you add another department in the future, you should move the pivoting to the client side:

SELECT r.ProfRankName,
    d.DeptID,
    SUM(f.Salary) as Total
FROM faculty f
INNER JOIN dept d ON f.DeptID = d.DeptID
INNER JOIN ProfRank r ON f.Salary BETWEEN r.Minimumm AND r.Maximum
GROUP BY d.DeptID, r.ProfRankID, r.ProfRankName