SQL Server行转列实现咨询：调整查询结果行列展示形式

To transform your row-based results into the columnar format you want, SQL Server's PIVOT operator is exactly what you need. Here's how to adjust your existing query:

SELECT 
    [Account Detail Tap], 
    [Travel Insurance Tap], 
    [Blacklist Tap]
FROM (
    -- Your original base query that fetches action names and their IsDeleted status
    SELECT [AM].ActionNameEnglish, [RSM].IsDeleted 
    FROM ScreenMaster [SM] 
    INNER JOIN ActionMaster [AM] ON [SM].ScreenID = [AM].ScreenID 
    INNER JOIN RoleScreenMapping [RSM] ON [AM].ActionID = [RSM].ActionID 
    WHERE ... -- Keep your existing WHERE clause conditions here
) AS SourceData
PIVOT (
    -- Use MAX (or MIN) since each action maps to exactly one IsDeleted value
    MAX(IsDeleted)
    -- Define which row values become new columns
    FOR ActionNameEnglish IN (
        [Account Detail Tap], 
        [Travel Insurance Tap], 
        [Blacklist Tap]
    )
) AS PivotedResults;

Key Details: #

• Why MAX(IsDeleted)? Since each action name in your source data has exactly one corresponding IsDeleted value, using MAX (or MIN) simply retrieves that single value without modifying it. This is required because PIVOT mandates an aggregate function.
• Square brackets for action names: Your action names include spaces, so wrapping them in square brackets avoids syntax errors in the IN clause.
• Dynamic columns (if needed): If your list of actions might grow or change over time, a static pivot like this will need manual updates. For a flexible solution, you could use dynamic SQL to auto-generate the list of action names, but that adds complexity—stick with the static version if your action set is fixed.

Running this adjusted query with your existing WHERE conditions should return the single-row result you're targeting, with each action name as a column and its IsDeleted value below it.

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