[Solved] Joining two SQL tables in Microsoft SQL server 2012 to get a certain format [closed]

by


After re-reading your question, I think this is what you are looking for:

SELECT  e.Department_ID ,
        e.Employee_Name AS Col1 ,
        e.Total_Hours
FROM    Employee e
UNION ALL
SELECT  d.Department_ID ,
        d.Department_Name AS Col1 ,
        SUM(e.Total_Hours)
FROM    Employee e
        JOIN Department d ON e.Department_ID = d.Department_ID
GROUP BY d.Department_ID ,
        d.Department_Name
ORDER BY Department_ID ASC ,
        Total_Hours DESC

SQL Fiddle Demo

Based on provided table structure and sample data, I feel that you may want to redesign your table something similar to my example above. It seems to violate the normal form and seems to be missing primary key in your tables. I strongly urge you to redesign your table so it saves you some headaches in the future. Meanwhile, here is how you can achieve your desired output: 

SELECT  e.Employee_Name AS Col1 ,
        e.Total_Hours,
        e.Department_Name as Sort_Field
FROM    Employee e
UNION ALL
SELECT  d.Department_Name AS Col1 ,
        d.Total_Hours,
        UPPER(d.Department_Name) as Sort_Field     
FROM   Department d
ORDER BY Sort_Field,Total_Hours desc

Sample Data Solution

solved Joining two SQL tables in Microsoft SQL server 2012 to get a certain format [closed]