SQL Server 2012：如何将员工关联设备记录转为视图列

To build the required view that pivots each employee's linked equipment into two columns, we'll use window functions and conditional aggregation. Here's a straightforward breakdown of the approach:

1. Number Equipment Entries: Assign a sequential row number (1, 2) to each equipment linked to an employee using ROW_NUMBER(). This helps us map entries to Equipment1Id and Equipment2Id.
2. Pivot Columns: Use MAX(CASE...) to aggregate the numbered equipment entries into separate columns per employee.
3. Generate View ID: Since views don't support native IDENTITY columns, we use ROW_NUMBER() ordered by employee ID to create the auto-incrementing Id column.
4. Fix Data Type Mismatch: Convert EquipmentMapping.EmployeeId from varchar(255) to int to match the view's required data type.

Final SQL Statement #

CREATE VIEW EmployeeEquipmentMappingView
AS
SELECT 
    ROW_NUMBER() OVER (ORDER BY e.Id) AS Id,
    e.Id AS EmployeeId,
    MAX(CASE WHEN numbered.RowNum = 1 THEN numbered.EquipmentId END) AS Equipment1Id,
    MAX(CASE WHEN numbered.RowNum = 2 THEN numbered.EquipmentId END) AS Equipment2Id
FROM Employee e
LEFT JOIN (
    SELECT 
        CAST(em.EmployeeId AS INT) AS EmployeeId,
        em.EquipmentId,
        ROW_NUMBER() OVER (PARTITION BY CAST(em.EmployeeId AS INT) ORDER BY em.EquipmentId) AS RowNum
    FROM EquipmentMapping em
) AS numbered ON e.Id = numbered.EmployeeId
GROUP BY e.Id;

Key Details #

• Including All Employees: The LEFT JOIN ensures employees with no equipment are still included in the view (both equipment columns will return NULL).
• Ordering: The ORDER BY em.EquipmentId in the window function sorts equipment IDs when assigning row numbers—adjust this clause if you need a different ordering logic.
• Constraint Compliance: Per your requirement that employees have no more than 2 equipments, this query will ignore any additional entries beyond the second.

内容的提问来源于stack exchange，提问作者Siddharth Bharadwaj