Skip to content
Advertisement

TSQL – Parent Child (1 to zero/many) Grouping/Aggregation

Code (Sample Data Staging):

Description:

@Emp is the sample Employee table (Unique Employee records).

  • EId = Employee Id
  • FN = First Name
  • LN = Last Name

@EmpPhCont is the sample Employee Phone Contact table (Each Emp from @Emp table can have zero, one, or multiple phone numbers here – unique by Emp/Type).

  • PhType = Phone Type (home, mobile, work, and etc)
  • PhNum = Phone Number
  • PhExt = Phone Extension (mostly available for “Work” PhType)
  • IsMain = Is it main contact number. Each employee with a phone num will have exactly 1 record marked as IsMain.
  • CreatedOn = Date the record was created

Goal:

To output 1 record per employee with the following Columns

EId | HomeNum | MobileNum | WorkNum | WorkNumExt | MainPhType

Rules:

Return all EId for all records from @Emp, whether they have a @EmpPhCont record or not.

For each emp that has @EmpPhCont record avail, return the newest created PhNum and PhExt for the corresponding PhType, UNLESS an older record for the same Emp/PhType is marked as IsMain = 1 (For any emp, for whichever PhType, if IsMain = 1, always return that PhNum and PhExt value).

Expected Output:

My unsuccessful try:

Advertisement

Answer

Use ROW_NUMBER() window function inside a CTE to get the rows from @EmpPhCont that you want returned and join this CTE to @Emp:

See the demo.
Results:

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement