SQL Server存储过程多行转单行多列实现方案咨询

Here's how you can modify your stored procedure to transform the result set from 2 rows (each with 6 doctor details columns) into a single row with 12 columns (each doctor's details prefixed with their row identifier). We'll use a CTE with row numbering combined with conditional aggregation—this approach is far more flexible than PIVOT when you need to transpose multiple columns at once.

Modified Stored Procedure Code #

CREATE PROCEDURE SP_BILL_FOOTER_DOCTOR 
    @subDepartmentId int 
AS 
BEGIN
    -- First, wrap the original query in a CTE to add row numbers to each doctor record
    WITH DoctorCTE AS (
        SELECT 
            ROW_NUMBER() OVER (ORDER BY HETC_MST_EMPLOYEE.EMPLOYEE_NAME) AS RowNum,
            HETC_MST_EMPLOYEE.EMPLOYEE_NAME,
            HETC_PAR_EMPLOYEE_TYPE.EMPLOYEE_TYPE_NAME,
            HETC_MST_DOCTOR_SPECIALITY.DOCTOR_SPECIALITY_DESCRIPTION,
            HETC_MST_SUB_DEPARTMENT.SUB_DEPARTMENT_NAME,
            HETC_MST_EMPLOYEE.DOCTOR_SIGNATURE,
            CASE 
                WHEN HETC_MST_EMPLOYEE.DOCTOR_SIGNATURE = '' THEN '' 
                ELSE ISNULL(SIGNATURE_PATH.DOCUMENT_PATH,'') + HETC_MST_EMPLOYEE.DOCTOR_SIGNATURE 
            END AS DOCTOR_SIGNATURE_PIC
        FROM HETC_MST_EMPLOYEE 
        INNER JOIN HETC_PAR_EMPLOYEE_TYPE 
            ON HETC_PAR_EMPLOYEE_TYPE.EMPLOYEE_TYPE_ID = HETC_MST_EMPLOYEE.EMPLOYEE_TYPE_ID 
        INNER JOIN HETC_MST_DOCTOR_SPECIALITY 
            ON HETC_MST_DOCTOR_SPECIALITY.DOCTOR_SPECIALITY_ID = HETC_MST_EMPLOYEE.DOCTOR_SPECIALITY_ID 
        INNER JOIN HETC_MST_DOCTOR_DEPARTMENT 
            ON HETC_MST_DOCTOR_DEPARTMENT.EMPLOYEE_ID = HETC_MST_EMPLOYEE.EMPLOYEE_ID 
        INNER JOIN HETC_MST_SUB_DEPARTMENT 
            ON HETC_MST_SUB_DEPARTMENT.SUB_DEPARTMENT_ID = HETC_MST_DOCTOR_DEPARTMENT.SUB_DEPARTMENT_ID 
        LEFT JOIN (
            SELECT DOCUMENT_PATH 
            FROM HETC_MST_DOCUMENT_PATH 
            INNER JOIN HETC_MST_TYPE_OF_ATTACHMENT 
                ON HETC_MST_DOCUMENT_PATH.TYPE_OF_DOCUMENT_ID = HETC_MST_TYPE_OF_ATTACHMENT.TYPE_OF_DOCUMENT_ID 
            WHERE HETC_MST_TYPE_OF_ATTACHMENT.TYPE_OF_DOCUMENT_CODE='DSI'
        ) AS SIGNATURE_PATH ON 1=1 
        WHERE HETC_MST_SUB_DEPARTMENT.SUB_DEPARTMENT_ID = @subDepartmentId
    )
    -- Use conditional aggregation to pivot all columns into a single row
    SELECT
        MAX(CASE WHEN RowNum = 1 THEN EMPLOYEE_NAME END) AS Doctor1_Name,
        MAX(CASE WHEN RowNum = 1 THEN EMPLOYEE_TYPE_NAME END) AS Doctor1_Type,
        MAX(CASE WHEN RowNum = 1 THEN DOCTOR_SPECIALITY_DESCRIPTION END) AS Doctor1_Specialty,
        MAX(CASE WHEN RowNum = 1 THEN SUB_DEPARTMENT_NAME END) AS Doctor1_SubDepartment,
        MAX(CASE WHEN RowNum = 1 THEN DOCTOR_SIGNATURE END) AS Doctor1_Signature,
        MAX(CASE WHEN RowNum = 1 THEN DOCTOR_SIGNATURE_PIC END) AS Doctor1_SignaturePic,
        -- Repeat the pattern for the second doctor
        MAX(CASE WHEN RowNum = 2 THEN EMPLOYEE_NAME END) AS Doctor2_Name,
        MAX(CASE WHEN RowNum = 2 THEN EMPLOYEE_TYPE_NAME END) AS Doctor2_Type,
        MAX(CASE WHEN RowNum = 2 THEN DOCTOR_SPECIALITY_DESCRIPTION END) AS Doctor2_Specialty,
        MAX(CASE WHEN RowNum = 2 THEN SUB_DEPARTMENT_NAME END) AS Doctor2_SubDepartment,
        MAX(CASE WHEN RowNum = 2 THEN DOCTOR_SIGNATURE END) AS Doctor2_Signature,
        MAX(CASE WHEN RowNum = 2 THEN DOCTOR_SIGNATURE_PIC END) AS Doctor2_SignaturePic
    FROM DoctorCTE;
END

How This Works #

1. Row Numbering: The ROW_NUMBER() function assigns a unique identifier (1, 2) to each doctor row. The ORDER BY clause ensures consistent numbering—you can swap EMPLOYEE_NAME with EMPLOYEE_ID or another column if you need a different sort order.
2. Conditional Aggregation: For each original column, we use MAX(CASE WHEN RowNum = X THEN ColumnName END) to pull the value from the X-th row into a dedicated new column. MAX() works here because we only expect one value per row number; MIN() would also work if you prefer.
3. Why Skip PIVOT?: The standard PIVOT operator is built to transpose values from a single column into multiple columns. Since you need to transpose 6 separate columns at once, conditional aggregation is simpler and avoids the complexity of chaining multiple PIVOT operations.

Quick Notes #

• If there's a possibility of more than 2 doctors being returned, just extend the pattern by adding more CASE statements for RowNum = 3, RowNum = 4, etc.
• If you want empty values instead of NULL for missing doctors, wrap each CASE statement in ISNULL(), e.g., ISNULL(MAX(CASE WHEN RowNum = 2 THEN EMPLOYEE_NAME END), '') AS Doctor2_Name.

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