在Microsoft SQL Server中实现行转列及解决子查询返回多值错误

Hey there! Let's break down what's going wrong with your query and walk through the correct way to get your desired output.

Why Your Query Throws an Error #

The error Subquery returned more than 1 value happens because your subqueries (like SELECT Reading FROM test_table WHERE ID = 1213 AND Parameter = 'Sr No.') are returning multiple rows. When you use a subquery as an expression (like assigning it to a column alias), SQL Server expects it to return exactly one value—but here, there are 3 rows for ID 1213 and Parameter 'Sr No.' (including nulls).

On top of that, your outer FROM test_table would repeat the result for every row in the table, which isn't what you want either.

Correct Solution to Get Your Desired Output #

Your target output is to split non-null Reading values into separate rows, with the corresponding column populated and others set to null. The simplest way to achieve this is using UNION ALL to combine two filtered result sets:

-- Get non-null Sr No. entries, with Name as null
SELECT 
    ID,
    Reading AS [Sr No.],
    CAST(NULL AS VARCHAR(50)) AS Name
FROM test_table
WHERE Parameter = 'Sr No.' 
  AND Reading IS NOT NULL

UNION ALL

-- Get non-null Name entries, with Sr No. as null
SELECT 
    ID,
    CAST(NULL AS VARCHAR(50)) AS [Sr No.],
    Reading AS Name
FROM test_table
WHERE Parameter = 'Name' 
  AND Reading IS NOT NULL

-- Order to match your expected output
ORDER BY ID, [Sr No.] DESC;

What This Does: #

• The first part selects all non-null Sr No. values, setting the Name column to null.
• The second part selects all non-null Name values, setting the Sr No. column to null.
• UNION ALL combines these two sets without removing duplicates (which we don't need here).
• The ORDER BY clause ensures rows with Sr No. values come first, matching your desired output order.

Alternative: Using Window Functions for Scalability #

If you have more parameters to handle later, you can use ROW_NUMBER() to assign unique numbers to each non-null entry per ID and Parameter, then pivot the results:

WITH RankedReadings AS (
    SELECT 
        ID,
        Parameter,
        Reading,
        -- Assign row number to non-null readings per ID + Parameter
        ROW_NUMBER() OVER (PARTITION BY ID, Parameter ORDER BY (SELECT NULL)) AS RowNum
    FROM test_table
    WHERE Reading IS NOT NULL
)
SELECT 
    ID,
    MAX(CASE WHEN Parameter = 'Sr No.' THEN Reading END) AS [Sr No.],
    MAX(CASE WHEN Parameter = 'Name' THEN Reading END) AS Name
FROM RankedReadings
GROUP BY ID, RowNum
ORDER BY ID, [Sr No.] DESC;

This approach scales better if you add more parameters later—you just need to add more CASE statements in the SELECT clause.

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