How to show Convert rows to column with grand total

Question

Thank you in advance for assistance!

What i am trying to accomplish is this: I have a table with different salary grades in the same department. How can I display the Salary Grades in the Columns, with the count below for each columns, and then show the GRAND to of salary grades for each department. This is what i have:

Dept_Num    Dept_Name   Salary_Grade    TOTAL_SalaryGrade
2005           Mrktg              1        39
2005           Mrktg              2        26
2005           Mrktg              3         5
2010           Payroll            1        20
2010           Payroll            2         8
2015           Acct               1         6
2015           Acct               3         6

Dept_Num    Dept_Name   Salary_Grade 1  Salary_Grade 2  Salary_Grade 3  
2005          Mrktg            39          26              5    
2010          Payroll          20           8              0    
2015          Acct              6           0              6    
TOTAL_SalaryGrade              65          34             11



SELECT[Dept_Num]
  ,[Dept_Name]
  [Salary_Grades],
  [TOTAL_Salary_Grades],  
 FROM [Employees]

Thank you for any assistance given!

Ignoring the misplaced comma in the query, first I would search for [pivot](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) . Then I would search for [how-to-get-the-sum-of-all-column-values-in-the-last-row-of-a-resultset](https://stackoverflow.com/questions/5015279/how-to-get-the-sum-of-all-column-values-in-the-last-row-of-a-resultset) — Utsav, Jun 09 '17 at 12:14
Thank you very much! I was able to get the columns in rows, but still having a bit of a problem getting the totals. I think i'm missing something. Kind thanks to you sir! — teelee, Jun 09 '17 at 13:05
Ok. You should have mentioned that and the query you used for the same so we could have focussed on the issue of getting total. ANyways looks like Ranjana's answer will be helpful for you. Cheers! — Utsav, Jun 09 '17 at 13:09

score 0 · Accepted Answer · answered Jun 09 '17 at 13:00

Try as follows:

CREATE TABLE EMPLOYEES  (Dept_Num  INT,  Dept_Name CHAR(20),  Salary_Grade  INT,  TOTAL_SalaryGrade INT )
INSERT INTO EMPLOYEES VALUES (2005,'Mrktg'  ,1,39 )
INSERT INTO EMPLOYEES VALUES (2005,'Mrktg'  ,2,26 )
INSERT INTO EMPLOYEES VALUES (2005,'Mrktg'  ,3, 5 )
INSERT INTO EMPLOYEES VALUES (2010,'Payroll',1,20 )
INSERT INTO EMPLOYEES VALUES (2010,'Payroll',2, 8 )
INSERT INTO EMPLOYEES VALUES (2015,'Acct'   ,1, 6 )
INSERT INTO EMPLOYEES VALUES (2015,'Acct'   ,3, 6 )

(SELECT convert(varchar,Dept_Num) [Dept_Num],Dept_Name, isnull([1],0) [Salary_Grade 1],isnull([2],0) [Salary_Grade 2],isnull([3],0) [Salary_Grade 3]  from 
            (
                select Dept_Num
                    , Dept_Name
                    , TOTAL_SalaryGrade
                    , Salary_Grade
                from EMPLOYEES
           ) x
            pivot 
            (
                 SUM(TOTAL_SalaryGrade)
                for Salary_Grade in ([1],[2],[3])
            ) p )

union all
select 'TOTAL_SalaryGrade','',[1],[2],[3]  from
(SELECT SUM (TOTAL_SalaryGrade) as total,Salary_Grade     FROM EMPLOYEES GROUP BY Salary_Grade ) b
pivot
(sum(total) for  Salary_Grade in ([1],[2],[3])) q

The result is:

*-----------------*----------*------------------*---------------*---------------*
|Dept_Num         | Dept_Name|  Salary_Grade 1  |Salary_Grade 2 |Salary_Grade 3|
*-----------------*----------*------------------*---------------*---------------
|2015             | Acct     |     6            |    0          |  6           |
*-----------------*----------*------------------*---------------*---------------
|2005             | Mrktg    |     39           |  26           |   5          |
*-----------------*----------*------------------*---------------*---------------
|2010             | Payroll  |     20           |  8            |  0           |
*-----------------*----------*------------------*---------------*---------------
|TOTAL_SalaryGrade|          |     65           |  34           |11            |
*-----------------*----------*------------------*---------------*---------------

How to show Convert rows to column with grand total

1 Answers1