基于D列动态选择目标列实现SQL Unpivot转换需求

Ah, I get it—you're stuck with a pivot-style table where each row's D column specifies which GRC-prefixed column holds the relevant value, and static solutions like hardcoded CASE statements or UNPIVOT aren't scalable when new GRC columns get added. The goal is to automatically pull the value matching the D column for each row, without updating your query every time a new target is added.

First, Let's Understand the Static Limitation #

A static approach would look something like this, but it's inflexible—you'd have to edit the CASE clause every time a new GRC column is added:

SELECT 
    Date AS DATE,
    D,
    CASE D
        WHEN 'GRC03' THEN GRC03
        WHEN 'GRC06' THEN GRC06
        WHEN 'GRC08' THEN GRC08
        WHEN 'GRC21' THEN GRC21
        WHEN 'GRC25' THEN GRC25
        WHEN 'GRC29' THEN GRC29
    END AS RESULTS
FROM YourTableName;

Dynamic SQL Solution (SQL Server) #

This approach dynamically generates the CASE logic based on distinct values in D, so it adapts automatically to new GRC columns:

DECLARE @CaseStmt NVARCHAR(MAX) = '';
DECLARE @TableName NVARCHAR(128) = 'YourTableName'; -- Replace with your actual table name

-- Build the CASE WHEN branches from distinct values in D
SELECT @CaseStmt += CONCAT(
    ' WHEN ''', D, ''' THEN ', QUOTENAME(D), CHAR(13)
)
FROM (SELECT DISTINCT D FROM YourTableName) AS DistinctD
WHERE D LIKE 'GRC%'; -- Filter to only GRC-prefixed columns

-- Assemble the full SQL query
DECLARE @Sql NVARCHAR(MAX) = CONCAT(
'SELECT 
    Date AS DATE,
    D,
    CASE D',
@CaseStmt,
' END AS RESULTS
FROM ', QUOTENAME(@TableName), ';'
);

-- Execute the dynamic query
EXEC sp_executesql @Sql;

Dynamic SQL Solution (MySQL) #

If you're working with MySQL, the syntax is slightly different, but the logic is the same:

SET @CaseStmt = '';
SET @TableName = 'YourTableName'; -- Replace with your actual table name

-- Build the CASE WHEN branches using GROUP_CONCAT
SELECT GROUP_CONCAT(
    CONCAT(' WHEN ''', D, ''' THEN `', D, '`') SEPARATOR ' '
) INTO @CaseStmt
FROM (SELECT DISTINCT D FROM YourTableName) AS DistinctD
WHERE D LIKE 'GRC%';

-- Assemble and execute the dynamic query
SET @Sql = CONCAT(
'SELECT 
    Date AS DATE,
    D,
    CASE D ', @CaseStmt, ' END AS RESULTS
FROM ', @TableName, ';'
);

PREPARE stmt FROM @Sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Key Notes #

• Validation: To avoid errors if D contains values that don't match actual column names, add a check first:

  -- SQL Server validation
  SELECT d.D
  FROM (SELECT DISTINCT D FROM YourTableName) d
  LEFT JOIN INFORMATION_SCHEMA.COLUMNS c 
      ON c.TABLE_NAME = 'YourTableName' 
      AND c.COLUMN_NAME = d.D
  WHERE c.COLUMN_NAME IS NULL;
  

  This will return any values in D that don't correspond to an existing column—you can handle these by adding an ELSE NULL (or custom message) to your CASE statement.
• Scalability: This solution works for 200+ GRC columns and automatically includes new ones as long as they appear in D.

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