CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Theva Deva
Theva Deva

Reputation: 63

Display all employees under each manager with role

In SQL Server 2008, I have a table tblStock with 5 columns:

Manager, Employee, Value1, Value2, Value3.

Sample data will be,

enter image description here

I have written the below code to get the above sample output:

 IF OBJECT_ID('tempdb..#tblDepartment') IS NOT NULL DROP TABLE #tblDepartment 

SELECT * INTO #tblDepartment
FROM
(
    SELECT 'Theva' Manager,  'Lawlrence' Employee, 10 Value1, 20 Value2, 60 Value3 
    UNION ALL 
    SELECT 'Theva', 'David', 20, 35, 42 
    UNION ALL 
    SELECT 'Theva', 'Ragav', 45, 35, 86 
    UNION ALL 
    SELECT 'Prem', 'Vino', 69, 99, 45 
    UNION ALL 
    SELECT 'Prem', 'Lara', 27, 99, 45 
    UNION ALL 
    SELECT 'Anzal', 'Ranjani', 65, 55, 12 
    UNION ALL 
    SELECT 'Anzal', 'Priya', 55, 47, 89 
    UNION ALL 
    SELECT 'Anzal', 'Vinoth', 98, 53, 56 
    UNION ALL 
    SELECT 'Rafeek', 'Ashok', 48, 75, 45
)TAB

Select * from #tblDepartment

I want to get the following output from the above table,

enter image description here

In this output, Which i mentioned green color row is sum of employee who belongs to particular manager.

Anyone know the solution for this.

Upvotes: 0

Views: 560

Answers (5)

Von Abanes
Von Abanes

Reputation: 716

This may not the OP's desired answer, I tried recreate it using SQL 2012 and this is were I came up. 

    IF OBJECT_ID('tempdb..#tblDepartment') IS NOT NULL DROP TABLE #tblDepartment 

    SELECT * INTO #tblDepartment
    FROM
    (
        SELECT 'Theva' Manager,  'Lawlrence' Employee, 10 Value1, 20 Value2, 60 Value3 
        UNION ALL 
        SELECT 'Theva', 'David', 20, 35, 42 
        UNION ALL 
        SELECT 'Theva', 'Ragav', 45, 35, 86 
        UNION ALL 
        SELECT 'Prem', 'Vino', 69, 99, 45 
        UNION ALL 
        SELECT 'Prem', 'Lara', 27, 99, 45 
        UNION ALL 
        SELECT 'Anzal', 'Ranjani', 65, 55, 12 
        UNION ALL 
        SELECT 'Anzal', 'Priya', 55, 47, 89 
        UNION ALL 
        SELECT 'Anzal', 'Vinoth', 98, 53, 56 
        UNION ALL 
        SELECT 'Rafeek', 'Ashok', 48, 75, 45
    )TAB

    DECLARE @T AS TABLE
        (
          ID INT IDENTITY(1, 1)
                 PRIMARY KEY ,
          NAME VARCHAR(50) ,
          Role VARCHAR(50) ,
          Value1 INT ,
          Value2 INT ,
          Value3 INT
        );
    WITH    CTE
              AS ( SELECT   Manager ,
                            Employee ,
                            SUM(Value1) AS Value1 ,
                            SUM(Value2) AS Value2 ,
                            SUM(Value3) AS Value3
                   FROM     #tblDepartment
                   GROUP BY Manager ,
                            Employee
                            WITH ROLLUP
                 )
        INSERT  INTO @T
                ( NAME, Role, Value1, Value2, Value3 )
    SELECT  Name ,
            Role ,
            Value1 ,
            Value2 ,
            Value3
    FROM    ( SELECT    ISNULL(Employee, Manager) NAME ,
                        IIF(Employee IS NULL, 'Manager', 'Employee') ROLE ,
                        Value1 ,
                        Value2 ,
                        Value3
              FROM      CTE
            ) T
    WHERE   t.NAME IS NOT NULL

    SELECT  Name ,
            Role ,
            Value1 ,
            Value2 ,
            Value3 FROM @T
    ORDER BY ID DESC

Result:

        Name                  Role               Value1      Value2      Value3
        --------------------- ------------------ ----------- ----------- -----------
        Theva                 Manager            75          90          188
        Ragav                 Employee           45          35          86
        Lawlrence             Employee           10          20          60
        David                 Employee           20          35          42
        Rafeek                Manager            48          75          45
        Ashok                 Employee           48          75          45
        Prem                  Manager            96          198         90
        Vino                  Employee           69          99          45
        Lara                  Employee           27          99          45
        Anzal                 Manager            218         155         157
        Vinoth                Employee           98          53          56
        Ranjani               Employee           65          55          12
        Priya                 Employee           55          47          89

Upvotes: 1

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4211

try this:

 declare @tb table(manager varchar(50),employee varchar(50), value1 int,value2 int,value3 int)
 insert into @tb
 SELECT 'Theva' Manager,  'Lawlrence' Employee, 10 Value1, 20 Value2, 60 Value3 
    UNION ALL 
    SELECT 'Theva', 'David', 20, 35, 42 
    UNION ALL 
    SELECT 'Theva', 'Ragav', 45, 35, 86 
    UNION ALL 
    SELECT 'Prem', 'Vino', 69, 99, 45 
    UNION ALL 
    SELECT 'Prem', 'Lara', 27, 99, 45 
    UNION ALL 
    SELECT 'Anzal', 'Ranjani', 65, 55, 12 
    UNION ALL 
    SELECT 'Anzal', 'Priya', 55, 47, 89 
    UNION ALL 
    SELECT 'Anzal', 'Vinoth', 98, 53, 56 
    UNION ALL 
    SELECT 'Rafeek', 'Ashok', 48, 75, 45

    select  case when len(employee)>0 then employee else a.manager end as Name,case when len(employee)>0 then 'Employee' else 'Manager' end as [Role],b.value1,b.value2,b.value3 from
    (select distinct manager,'Manager' as Role from @tb
    union all
    select employee,'employee' as Role from @tb) as a



    left join


    (select distinct a.manager,employee,value1,value2,value3 from
    (select manager,'' as employee,sum(value1)value1,sum(value2)value2,sum(value3)value3 from @tb group by manager
    union all
    select a.manager,b.employee,b.value1,b.value2,b.value3 from
    (select manager,sum(value1)value1,sum(value2)value2,sum(value3)value3 from @tb group by manager) as a
    left join
    (select * from @tb) as b
    on a.manager = b.manager) as a) as b
    on a.manager = b.manager where value1 is not null

result:

Anzal   Manager     218 155 157
Priya   Employee    55  47  89
Ranjani Employee    65  55  12
Vinoth  Employee    98  53  56
Prem    Manager     96  198 90
Lara    Employee    27  99  45
Vino    Employee    69  99  45
Rafeek  Manager     48  75  45
Ashok   Employee    48  75  45
Theva   Manager     75  90  188
David   Employee    20  35  42
Lawlrence Employee  10  20  60
Ragav   Employee    45  35  86

Upvotes: 1

Rigerta
Rigerta

Reputation: 4039

This is the closest to your result, considering also your order:

select Name, Role, Value1, Value2, Value3
from (
       select t.MgrId, t.manager as Name, 'Manager' as Role, sum(t.Value1) Value1, sum(t.Value2) Value2, sum(t.Value3) Value3
       from (select *, dense_rank () OVER(ORDER BY Manager desc) AS MgrId from  #tblDepartment ) t
       group by t.MgrId, t.manager

       union all

       select t.MgrId, t.employee as Name, 'Employee' as Role, sum(t.Value1) Value1, sum(t.Value2) Value2, sum(t.Value3) Value3
       from (select *, dense_rank () OVER(ORDER BY Manager desc) AS MgrId from  #tblDepartment ) t
       group by  t.MgrId, t.employee
   ) t 
order by t.MgrId, t.Role desc

You can check a working version here. The ranking functions are available as of SQL Server 2008 so it should work for you too.

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82534

Here is one way to do it:

SELECT Name, Role, Value1, Value2, Value3
FROM
(
SELECT  Manager As Name, 
        'Manager' As Role, 
        SUM(Value1) As Value1, 
        SUM(Value2) AS Value2, 
        SUM(Value3)  AS Value3,
        Manager
FROM #tblDepartment 
GROUP BY Manager

UNION ALL

SELECT Employee As Name,
        'Employee' As Role, 
        Value1, 
        Value2, 
        Value3,
        Manager
FROM #tblDepartment 
) t
ORDER BY Manager,Role DESC, Name

It's not exactly the same order as yours, but the employees are under the correct manager.

See a live demo on rextester.

Upvotes: 2

Kevin
Kevin

Reputation: 781

Another way to get your result

WITH mCTE AS (
   SELECT a.Manager, a.Manager AS Employee, 'Manager' AS Role, SUM(a.Value1) AS Value1, SUM(a.Value2) AS Value2, SUM(a.Value3) AS Value3, 1 AS r
   FROM #tblDepartment AS a
   GROUP BY a.Manager

   UNION ALL

   SELECT a.Manager, a.Employee, 'Employee' AS Role, a.Value1, a.Value2, a.Value3, 2 AS r
   FROM #tblDepartment AS a
)

SELECT Employee, Role, Value1, Value2, Value3
FROM mCTE
ORDER BY Manager, r

Upvotes: 2

Related Questions