SQL中行转列实现：基于告警统计查询结果的行列转换需求

Hey there! It looks like you need to pivot your row-based alarm statistics into a columnar format—where each circlename gets a single row, with columns for each alarm type showing their respective counts. Let's walk through two reliable ways to do this, depending on your database system.

Method 1: Using CASE WHEN (Works with Most Databases) #

This is a universal approach that works across MySQL, PostgreSQL, SQL Server, and more. We'll use your original grouped query as a subquery, then use conditional aggregation to turn each alarm type into a column.

SELECT 
    circlename,
    SUM(CASE WHEN alarmname = 'Site Down' THEN count ELSE 0 END) AS `Site Down`,
    SUM(CASE WHEN alarmname = 'Predicted_Site Down' THEN count ELSE 0 END) AS `Predicted_Site Down`,
    SUM(CASE WHEN alarmname = 'Mains Fail' THEN count ELSE 0 END) AS `Mains Fail`,
    SUM(CASE WHEN alarmname = 'Fire & Smoke' THEN count ELSE 0 END) AS `Fire & Smoke`,
    SUM(CASE WHEN alarmname = 'Shelter Temperature High' THEN count ELSE 0 END) AS `Shelter Temperature High`
FROM (
    -- Your original grouped query
    SELECT 
        circlename, 
        alarmname, 
        COUNT(alarmname) AS count 
    FROM temip_alarm 
    WHERE alarmname IN ('Site Down', 'Predicted_Site Down', 'Mains Fail', 'Fire & Smoke', 'Shelter Temperature High')
    GROUP BY circlename, alarmname
) AS alarm_subquery
GROUP BY circlename;

How this works: #

• The subquery generates your original row-level counts for each circle and alarm.
• The outer query uses CASE WHEN to check each row's alarm type: if it matches the column we're building, we use the count; otherwise, we use 0.
• SUM() aggregates these values per circlename, giving us a single value for each alarm type per circle.

Method 2: Using PIVOT (For SQL Server/Oracle) #

If you're using a database that supports the PIVOT operator (like SQL Server or Oracle), you can use this more concise syntax:

SELECT 
    circlename,
    [Site Down],
    [Predicted_Site Down],
    [Mains Fail],
    [Fire & Smoke],
    [Shelter Temperature High]
FROM (
    -- Your original grouped query
    SELECT 
        circlename, 
        alarmname, 
        COUNT(alarmname) AS count 
    FROM temip_alarm 
    WHERE alarmname IN ('Site Down', 'Predicted_Site Down', 'Mains Fail', 'Fire & Smoke', 'Shelter Temperature High')
    GROUP BY circlename, alarmname
) AS alarm_subquery
PIVOT (
    SUM(count) -- Aggregation function (SUM/MAX works here since each group has one row)
    FOR alarmname IN ([Site Down], [Predicted_Site Down], [Mains Fail], [Fire & Smoke], [Shelter Temperature High])
) AS pivot_results;

How this works: #

• The subquery provides the base data (circle, alarm type, count).
• The PIVOT clause takes the distinct values from alarmname and turns them into columns, using SUM(count) to populate the values for each circle.

Important Notes: #

• If your alarm types might change over time (new alarms added), these static queries will need updates. For dynamic pivoting, you'd need to use database-specific dynamic SQL (e.g., using stored procedures in SQL Server or prepared statements in MySQL).
• Notice the backticks (MySQL) or square brackets (SQL Server/Oracle) around column names with spaces/special characters—this ensures the database interprets them correctly.

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