SQL Server中计算每行各列agree值总数的SQL查询方案

Got it, let's work through how to add that totalagree column you need! The core idea is to check each column in a row to see if it equals 'agree', then sum up all those matches.

Option 1: Manual CASE Statement (Straightforward for Known Columns) #

If you don't mind listing out each column (or can copy-paste and adjust quickly), you can use a series of CASE expressions that return 1 for 'agree' and 0 otherwise, then sum them up:

SELECT 
    *, -- Includes all your original 46 columns
    (
        CASE WHEN column_1 = 'agree' THEN 1 ELSE 0 END +
        CASE WHEN column_2 = 'agree' THEN 1 ELSE 0 END +
        -- Repeat this pattern for every one of your 46 columns
        -- ...
        CASE WHEN column_46 = 'agree' THEN 1 ELSE 0 END
    ) AS totalagree
FROM your_dataset_table; -- Replace with your actual table name

Just swap column_1 through column_46 with your real column names, and your_dataset_table with the name of your table.

Option 2: Dynamic SQL (Saves Time for 46 Columns) #

Writing 46 CASE statements manually is tedious—so let's use dynamic SQL to generate the query automatically. This pulls all column names from your table and builds the sum expression for you:

DECLARE @sql_query NVARCHAR(MAX);

-- Build the dynamic query string
SELECT @sql_query = 'SELECT *, (' + 
    STRING_AGG('CASE WHEN ' + QUOTENAME(name) + ' = ''agree'' THEN 1 ELSE 0 END', ' + ') + 
    ') AS totalagree FROM your_dataset_table;'
FROM sys.columns
WHERE object_id = OBJECT_ID('your_dataset_table'); -- Replace with your table name

-- Execute the generated query
EXEC sp_executesql @sql_query;

This will automatically include every column in your table, so you don't have to list them all out. The QUOTENAME function handles any column names with spaces or special characters, which is handy if your columns have descriptive names.

Quick Notes: #

• Make sure to replace your_dataset_table with the actual name of your table in both options.
• If any columns can have NULL values, this logic still works—NULL won't equal 'agree', so it will count as 0, which is probably what you want.

内容的提问来源于stack exchange，提问作者zain ul abidin