012. Building data up by columns

This is done by a series of sub-datasets Inner Joined together, one after the other.
Counts per area are joined in one at a time.
The join done using a single ID that is present throughout all the tables.

 
— TABLES:
— Table1
If Object_ID(‘AreaTable’, ‘U’) Is Not Null Drop Table AreaTable
Create Table [AreaTable](
[AreaID] [int] Null,
[Area] [nvarchar](50) Null
) On [Primary]
Insert [AreaTable] ([AreaID], [Area]) Values (1, ‘Area1’)
Insert [AreaTable] ([AreaID], [Area]) Values (2, ‘Area2’)
Insert [AreaTable] ([AreaID], [Area]) Values (3, ‘Area3’)

 
— Table2
If Object_ID(‘CourseTable’, ‘U’) Is Not Null Drop Table CourseTable
Create Table [CourseTable](
[CourseID] [int] Null,
[AreaID] [int] Null
) On [Primary]
Insert [CourseTable] ([CourseID], [AreaID]) Values (1, 1)
Insert [CourseTable] ([CourseID], [AreaID]) Values (2, 2)
Insert [CourseTable] ([CourseID], [AreaID]) Values (3, 1)
Insert [CourseTable] ([CourseID], [AreaID]) Values (4, 3)

 
— Table3
If Object_ID(‘AttendeeTable’, ‘U’) Is Not Null Drop Table AttendeeTable
Create Table [AttendeeTable](
[AttendeeID] [int] Null,
[AreaID] [int] Null
) On [Primary]
Insert [AttendeeTable] ([AttendeeID], [AreaID]) Values (1, 1)
Insert [AttendeeTable] ([AttendeeID], [AreaID]) Values (2, 2)
Insert [AttendeeTable] ([AttendeeID], [AreaID]) Values (3, 3)
Insert [AttendeeTable] ([AttendeeID], [AreaID]) Values (4, 3)
Insert [AttendeeTable] ([AttendeeID], [AreaID]) Values (5, 4)

 
— Table4
If Object_ID(‘TeacherAreaTable’, ‘U’) Is Not Null Drop Table TeacherAreaTable
Create Table [TeacherAreaTable](
[TeacherID] [int] Null,
[AreaID] [int] Null
) On [Primary]
Insert [TeacherAreaTable] ([TeacherID], [AreaID]) Values (1, 1)
Insert [TeacherAreaTable] ([TeacherID], [AreaID]) Values (2, 2)
Insert [TeacherAreaTable] ([TeacherID], [AreaID]) Values (3, 3)
Insert [TeacherAreaTable] ([TeacherID], [AreaID]) Values (1, 4)

 
— Table5
If Object_ID(‘TestResultTable’, ‘U’) Is Not Null Drop Table TestResultTable
Create Table [TestResultTable](
[ResultID] [int] Null,
[AreaID] [int] Null,
[PassID] [int] Null,
[FailID] [int] Null
) On [Primary]
Insert [TestResultTable] ([ResultID], [AreaID], [PassID], [FailID]) Values (1, 1, 1, 0)
Insert [TestResultTable] ([ResultID], [AreaID], [PassID], [FailID]) Values (2, 1, 0, 1)
Insert [TestResultTable] ([ResultID], [AreaID], [PassID], [FailID]) Values (3, 2, 1, 0)
Insert [TestResultTable] ([ResultID], [AreaID], [PassID], [FailID]) Values (4, 2, 0, 1)
Insert [TestResultTable] ([ResultID], [AreaID], [PassID], [FailID]) Values (5, 3, 1, 0)
Insert [TestResultTable] ([ResultID], [AreaID], [PassID], [FailID]) Values (6, 3, 1, 0)

 
Select
AreaTable.Area
,CountTable1.Count1 As CoursesCount
,CountTable2.Count2 As AttendeesCount
,CountTable3.Count3 As TeachersCount
,CountTable4.Count4 As PassCount
,CountTable4.Count5 As FailCount
From
AreaTable

Left Outer Join — For first lot of counts per area.
(
Select
AreaID
,Count(CourseID) As Count1
From
CourseTable
Group By
AreaID
) As CountTable1 On AreaTable.AreaID = CountTable1.AreaID

Left Outer Join — For second lot of counts per area.
(
Select
AreaID
,Count(AttendeeID) As Count2
From
AttendeeTable
Group By
AreaID
) As CountTable2 On AreaTable.AreaID = CountTable2.AreaID

Left Outer Join — For third lot of counts per area.
(
Select
AreaID
,Count(TeacherID) As Count3
From
TeacherAreaTable
Group By
AreaID
) As CountTable3 On AreaTable.AreaID = CountTable3.AreaID

Left Outer Join — For fourth lot of counts per area.
(
Select
AreaID
,Sum(PassID) As Count4
,Sum(FailID) As Count5
From
TestResultTable
Group By
AreaID
) As CountTable4 On AreaTable.AreaID = CountTable4.AreaID