SQL 2012垂直表转水平表：员工对应工位精准查询需求

Hey there! Let's figure out how to fix that vertical-to-horizontal pivot issue for your employee-workstation assignment table in SQL Server 2012. I know your current query is pulling all workstation data instead of grouping them per employee—let's fix that.

First, let's start with a common example of what your source table might look like (feel free to swap in your actual column names if they're different):

CREATE TABLE EmployeeWorkstations (
    EmployeeID INT,
    EmployeeName VARCHAR(50),
    WorkstationID VARCHAR(20),
    WorkstationLocation VARCHAR(100)
);

Depending on whether you want workstations listed in a single comma-separated column or split into separate columns, here are the two go-to solutions for SQL 2012:

1. Combine All Workstations into One Column #

Since SQL Server 2012 doesn't have the newer STRING_AGG() function, we'll use FOR XML PATH('') to concatenate values per employee:

SELECT 
    base.EmployeeID,
    base.EmployeeName,
    -- Remove the leading comma with STUFF()
    STUFF(
        (SELECT ', ' + ws.WorkstationID 
         FROM EmployeeWorkstations ws 
         WHERE ws.EmployeeID = base.EmployeeID
         FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),
        1, 2, ''
    ) AS AssignedWorkstations
FROM EmployeeWorkstations base
GROUP BY base.EmployeeID, base.EmployeeName;

• The TYPE clause makes sure special characters (like & or <) don't get mangled into XML entities.
• If you want to include workstation locations instead of IDs, just swap WorkstationID with WorkstationLocation in the subquery.

2. Split Workstations into Separate Columns (Fixed Count) #

If you know the maximum number of workstations any employee has, you can use PIVOT to turn rows into distinct columns. First, we need to rank each workstation per employee:

WITH RankedWorkstations AS (
    SELECT 
        EmployeeID,
        EmployeeName,
        WorkstationID,
        -- Assign a unique rank to each workstation for the employee
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY WorkstationID) AS WorkstationRank
    FROM EmployeeWorkstations
)
SELECT 
    EmployeeID,
    EmployeeName,
    [1] AS Workstation1,
    [2] AS Workstation2,
    [3] AS Workstation3 -- Add more columns if your max count is higher
FROM RankedWorkstations
PIVOT (
    MAX(WorkstationID)
    FOR WorkstationRank IN ([1], [2], [3])
) AS PivotResult;

• If you don't know the maximum number of workstations upfront, you'd need to use dynamic SQL to build the pivot columns dynamically. Let me know if you need that code!

Quick Fix for Your Original Query #

Chances are your initial query was missing a partition or group by employee. Without partitioning, the pivot was aggregating across all employees instead of grouping them individually—so that's why you got all workstation data mixed together.

If your table has different column names than my example, just tweak the code to match your schema, and it should work perfectly.

内容的提问来源于stack exchange，提问作者Cole Perrault