如何实现跨表行转列关联查询：将表X的行作为另一表的列

首先得先明确两张表的命名（你没给第二张表名字，我暂时叫它format_status，表X叫activity_info），核心问题是第二张表是宽表结构（把ID作为列名），我们需要先把它转成窄表（行存储FORMAT、ID、对应状态），再和表X关联就能得到想要的结果。下面分几种场景给你方案：

场景1：ID数量少，用静态SQL快速实现 #

如果你的ID只有49、50这类少量值，直接用CASE WHEN就能搞定，不用复杂的转置：

SELECT 
    ai.COLNAME AS ACTIVITY,
    CASE ai.ID
        WHEN 49 THEN fs.`49`
        WHEN 50 THEN fs.`50`
        -- 有其他ID的话继续加WHEN分支
    END AS `YES/NO`
FROM activity_info ai
JOIN format_status fs ON ai.FORMATID = fs.FORMAT
-- 过滤掉NA的结果，只保留YES/NO
WHERE CASE ai.ID
        WHEN 49 THEN fs.`49`
        WHEN 50 THEN fs.`50`
      END IN ('YES', 'NO');

场景2：ID数量多，用行转列（UNPIVOT/UNION ALL）实现 #

如果ID列很多（比如1到50全有），手动写CASE太麻烦，这时候用行转列的方式把宽表转成窄表，再关联表X：

适用于SQL Server/Oracle（支持UNPIVOT） #

WITH unpivoted_format AS (
    SELECT FORMAT, CAST(id_col AS INT) AS ID, status_val
    FROM format_status
    UNPIVOT (
        -- 把所有ID列转成status_val值，id_col存列名（即ID）
        status_val FOR id_col IN ([1], [2], [3], ..., [49], [50])
    ) AS unpvt
)
SELECT 
    ai.COLNAME AS ACTIVITY,
    uf.status_val AS [YES/NO]
FROM activity_info ai
JOIN unpivoted_format uf 
    ON ai.FORMATID = uf.FORMAT 
    AND ai.ID = uf.ID
WHERE uf.status_val IN ('YES', 'NO');

适用于MySQL（无UNPIVOT，用UNION ALL转置） #

WITH unpivoted_format AS (
    SELECT FORMAT, 1 AS ID, `1` AS status_val FROM format_status
    UNION ALL SELECT FORMAT, 2 AS ID, `2` AS status_val FROM format_status
    -- ... 这里把1到50的列都用UNION ALL拼起来
    UNION ALL SELECT FORMAT, 49 AS ID, `49` AS status_val FROM format_status
    UNION ALL SELECT FORMAT, 50 AS ID, `50` AS status_val FROM format_status
)
SELECT 
    ai.COLNAME AS ACTIVITY,
    uf.status_val AS `YES/NO`
FROM activity_info ai
JOIN unpivoted_format uf 
    ON ai.FORMATID = uf.FORMAT 
    AND ai.ID = uf.ID
WHERE uf.status_val IN ('YES', 'NO');

场景3：ID列动态变化（自动适配所有ID列） #

如果后续ID列会增减，不想每次改SQL，可以用动态SQL自动生成转置的列列表，以SQL Server为例：

DECLARE @cols NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);

-- 自动获取format_status表中除了FORMAT以外的所有列（即ID列）
SELECT @cols = STRING_AGG(QUOTENAME(COLUMN_NAME), ',')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'format_status' AND COLUMN_NAME <> 'FORMAT';

-- 拼接动态查询语句
SET @query = '
WITH unpivoted_format AS (
    SELECT FORMAT, CAST(id_col AS INT) AS ID, status_val
    FROM format_status
    UNPIVOT (
        status_val FOR id_col IN (' + @cols + ')
    ) AS unpvt
)
SELECT 
    ai.COLNAME AS ACTIVITY,
    uf.status_val AS [YES/NO]
FROM activity_info ai
JOIN unpivoted_format uf 
    ON ai.FORMATID = uf.FORMAT 
    AND ai.ID = uf.ID
WHERE uf.status_val IN (''YES'', ''NO'')
';

-- 执行动态SQL
EXEC sp_executesql @query;

核心思路总结 #

1. 把format_status的宽表结构转成每行对应一个FORMAT+ID+状态的窄表；
2. 通过FORMATID和ID两个字段和activity_info关联；
3. 过滤掉NA值，只保留YES/NO的结果，最终输出COLNAME和对应状态。

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