I have the tables
create table #EmployeeMemberMaster(EmployeeID int identity(1,1), EmployeeName nvarchar(250), NationalityID int, EmployeeSystemID int) insert into #EmployeeMemberMaster (EmployeeName,NationalityID,EmployeeSystemID) select 'IQ', 1, 1 union select 'JK', 3, 6 union select 'JOP',1, 4 union select 'IPO', 4, 3 union select 'RERT', 1, 2 union select 'GT',1, 2 union select 'Kakka', 1, 1 union select 'Wala Weber', 1, 1 union select 'Coffee Bean', 1, 1 select * from #EmployeeMemberMaster create table #NationalityMaster(NationalityID int, NationalityName nvarchar(150)) insert into #NationalityMaster select 1, 'Saudi Arabia' union select 2, 'India' union select 3, 'United States' union select 4, 'United Kingdom' union select 5, 'Canada' union select 6, 'Australia' union select 7, 'Singapore' select * from #NationalityMaster create table #Gendermaster(GenderID int, GenderName nvarchar(150)) insert into #Gendermaster select 1, 'Male' union select 2, 'Female' select * from #Gendermaster
I have written query:
CREATE TABLE #tempSectorData ( SaudiCounts bigint NULL ,NonSaudiCounts bigint NULL ,MaleCounts bigint NULL ,FeMaleCounts bigint NULL ); select E.EmployeeID,E.MemberID,N.NationalityTypeID into #tempNationality1 from EmployeeMemberMaster E Inner join NationalityMaster N ON E.NationalityID = N.NationalityID Inner join NationalityTypeMaster NT ON N.NationalityTypeID = NT.NationalityTypeID WHERE ISNULL(E.IsDeleted, 0) = 0 select NationalityTypeID, Count(NationalityTypeID) as Nationalitycount into #tempNationality2 from #tempNationality1 Group by NationalityTypeID order by NationalityTypeID asc; INSERT INTO #tempSectorData (SaudiCounts) SELECT ISNULL(V.Nationalitycount,0) as SaudiCounts FROM #tempNationality2 V WHERE V.NationalityTypeID = 1 INSERT INTO #tempSectorData (NonSaudiCounts) SELECT ISNULL(V.Nationalitycount,0) as NonSaudiCounts FROM #tempNationality2 V WHERE V.NationalityTypeID <> 1 select E.EmployeeID,E.MemberID,N.GenderID into #tempGender1 from EmployeeMemberMaster E Inner join GenderMaster N ON E.GenderID = N.GenderID WHERE ISNULL(E.IsDeleted, 0) = 0 select GenderID, Count(GenderID) as GenderCount into #tempGender2 from #tempGender1 Group by GenderID order by GenderID asc; INSERT INTO #tempSectorData (MaleCounts) SELECT ISNULL(V.GenderCount,0) as MaleCounts FROM #tempGender2 V WHERE V.GenderID = 1 INSERT INTO #tempSectorData (FeMaleCounts) SELECT ISNULL(V.GenderCount,0) as FeMaleCounts FROM #tempGender2 V WHERE V.GenderID = 2 select * from #tempSectorData DROP TABLE #tempNationality1 DROP TABLE #tempNationality2 DROP TABLE #tempGender1 DROP TABLE #tempGender2 DROP TABLE #tempSectorData
This query returns the result in this way:
Expected result is this way below:
How to achieve this. Please help on this.