Group by headname and print headname once

1

how do i get output as like below from the below query

Headname Name
Fabro Canon
Estacio
Fajardo
Sobrevilla
Teruel Alabaso
Bahadur
Casia
jayan
Tano

-- code begin here

create Table #Employee
(
Empid int primary key identity(1,1),
Name varchar(255),
Designation varchar(255),
status int
)
insert into #Employee(Name,Designation,status) values('jayan','Driver',1)
insert into #Employee(Name,Designation,status) values('Casia','Mechanic',1)
insert into #Employee(Name,Designation,status) values('Alabaso','Mechanic',1)
insert into #Employee(Name,Designation,status) values('Bahadur','Mechanic',1)
insert into #Employee(Name,Designation,status) values('Tano','Mechanic',1)
insert into #Employee(Name,Designation,status) values('Canon','Helper',1)
insert into #Employee(Name,Designation,status) values('Teruel','Supervisor',1)
insert into #Employee(Name,Designation,status) values('Estacio','Driver',1)
insert into #Employee(Name,Designation,status) values('Fabro','Supervisor',1)
insert into #Employee(Name,Designation,status) values('Fajardo','Driver',1)
insert into #Employee(Name,Designation,status) values('Sobrevilla','Driver',1)

create table #DeptHead
(
HeadID int primary key identity(1,1),
HeadName varchar(200),
Fk_Empid int FOREIGN KEY REFERENCES #Employee(Empid),
deptID int,
Deptname varchar(200),
status int
)
insert into #DeptHead(HeadName,Fk_Empid,deptID,Deptname,status) values('Teruel',7,1,'Workshop',1)
insert into #DeptHead(HeadName,Fk_Empid,deptID,Deptname,status) values('Fabro',9,2,'Operation',1)

create table #ReportingMgr
(
Id int primary key identity(1,1),
Fk_HeadId int FOREIGN KEY REFERENCES #DeptHead(HeadID),
FK_Empid int FOREIGN KEY REFERENCES #Employee(Empid),
Status int
)
insert into #ReportingMgr(Fk_HeadId,Fk_Empid,Status) values(7,1,1)
insert into #ReportingMgr(Fk_HeadId,Fk_Empid,Status) values(7,2,1)
insert into #ReportingMgr(Fk_HeadId,Fk_Empid,Status) values(7,3,1)
insert into #ReportingMgr(Fk_HeadId,Fk_Empid,Status) values(7,4,1)
insert into #ReportingMgr(Fk_HeadId,Fk_Empid,Status) values(7,5,1)
insert into #ReportingMgr(Fk_HeadId,Fk_Empid,Status) values(9,6,1)
insert into #ReportingMgr(Fk_HeadId,Fk_Empid,Status) values(9,8,1)
insert into #ReportingMgr(Fk_HeadId,Fk_Empid,Status) values(9,10,1)
insert into #ReportingMgr(Fk_HeadId,Fk_Empid,Status) values(9,11,1)

select dense_rank() over(order by HeadName) as rowid, HeadName,Name,Empid from #DeptHead d
inner join
#ReportingMgr r on d.Fk_Empid = r.Fk_HeadId
inner join
#Employee e on e.Empid = R.Fk_Empid
group by HeadName,Name,Empid

drop table #Employee
drop table #DeptHead
drop table #ReportingMgr

2

hi

hope this helps :slight_smile:

;WITH cte
AS (
       SELECT
            HeadName
            , Name
       FROM
            #ReportingMgr a
            JOIN #DeptHead AS DH
                ON a.Fk_HeadId = DH.Fk_Empid
            JOIN #Employee AS E
                ON E.Empid = a.FK_Empid
   ) , cte_rownum AS 
(
SELECT  ROW_NUMBER()OVER(PARTITION BY cte.HeadName ORDER BY name ) AS rn , * FROM  cte 
)
SELECT  CASE WHEN rn <> 1 THEN '' ELSE cte_rownum.HeadName END AS HeadName, cte_rownum.Name FROM cte_rownum

image

1 Like
3

:+1:

4

The following is no faster but it is a bit shorter and, perhaps, easier to read, if you have SQLServer 2012 or above.

 SELECT  HeadName = IIF(LAG(d.HeadName,1,'') OVER (PARTITION BY d.HeadName ORDER BY e.Name) = d.HeadName,'',d.HeadName)
        ,e.Name 
   FROM #Employee     e 
   JOIN #ReportingMgr r ON e.Empid    = r.Fk_Empid
   JOIN #DeptHead     d ON d.Fk_Empid = r.Fk_HeadId
  ORDER BY d.HeadName, e.Name
;
5

thank you jeff :slight_smile: