SQL行转列求助：将CPU/MEM使用率按日期横向展示

Looks like you're trying to reshape your query results so each row shows a single date with both CPU and MEM usage as separate columns—totally get why the pivot might have tripped you up, let's walk through two solid solutions.

Option 1: Conditional Aggregation (Simple & Widely Compatible) #

This approach uses CASE WHEN to explicitly pull out CPU and MEM values, then groups by date. It's often easier to debug than PIVOT and works across most SQL databases:

SELECT
    SAMPLE_TIME,
    MAX(CASE WHEN STAT_GROUP = 'CPU' THEN PERCENTAGE END) AS CPU_USAGE,
    MAX(CASE WHEN STAT_GROUP = 'MEM' THEN PERCENTAGE END) AS MEM_USAGE
FROM (
    -- Your original query to calculate percentages
    SELECT
        SAMPLE_TIME,
        STAT_GROUP,
        CONVERT(decimal(16,2), STAT_VALUE/100.0) AS PERCENTAGE
    FROM VPXV_HIST_STAT_YEARLY
    WHERE
        ENTITY LIKE 'vm-1783'
        AND SAMPLE_TIME > '2017-10-01'
        AND SAMPLE_TIME < '2017-12-31'
        AND STAT_GROUP IN ('CPU', 'MEM')
        AND STAT_NAME = 'USAGE'
) AS SourceData
GROUP BY SAMPLE_TIME
ORDER BY SAMPLE_TIME ASC;

Why this works: #

• The subquery first generates your original calculated percentages for CPU and MEM.
• The outer query groups by SAMPLE_TIME, then uses MAX() (or MIN()—either works here since each date/STAT_GROUP has one value) to grab the matching CPU or MEM value for each date.

Option 2: Using PIVOT (SQL Server-Specific) #

If you want to stick with PIVOT, the key is to first prepare your source data (with the calculated percentage) and then pivot on STAT_GROUP. Here's the correct syntax:

SELECT
    SAMPLE_TIME,
    CPU AS CPU_USAGE,
    MEM AS MEM_USAGE
FROM (
    -- Your original query to calculate percentages
    SELECT
        SAMPLE_TIME,
        STAT_GROUP,
        CONVERT(decimal(16,2), STAT_VALUE/100.0) AS PERCENTAGE
    FROM VPXV_HIST_STAT_YEARLY
    WHERE
        ENTITY LIKE 'vm-1783'
        AND SAMPLE_TIME > '2017-10-01'
        AND SAMPLE_TIME < '2017-12-31'
        AND STAT_GROUP IN ('CPU', 'MEM')
        AND STAT_NAME = 'USAGE'
) AS SourceData
PIVOT (
    MAX(PERCENTAGE) -- Aggregate function (we use MAX since only one value per group)
    FOR STAT_GROUP IN ([CPU], [MEM]) -- Columns to pivot
) AS PivotTable
ORDER BY SAMPLE_TIME ASC;

Common Pivot Pitfalls You Might Have Hit: #

• Forgetting to wrap the original calculation in a subquery (PIVOT needs a clean source dataset with the columns to pivot).
• Using the wrong aggregate function—since each date/STAT_GROUP pair has exactly one row, MAX() or MIN() will return the correct value (you can't use SUM() here unless you have multiple rows per group, which you don't).
• Not enclosing CPU and MEM in square brackets (required if your column values have spaces, but it's good practice here anyway).

Either of these queries should give you the output you want: one row per SAMPLE_TIME, with separate columns for CPU and MEM usage percentages.

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