在SQL Server上使用Case

Question

我试图获得行的特定值参考ID，并给它一个结果:selected从这些列的行.

例如，我有一个这样的表:

member_typeCarMemberTaxiMemberBusMemberCarMemberBusMember

这里不需要CTE。只需使用聚合函数检索所需的结果。

-- SQL Server (v2017)
SELECT member_id
, MAX(CASE WHEN member_type = 'CarMember' THEN member_number END) CarMember
, MAX(CASE WHEN member_type = 'TaxiMember' THEN member_number END) TaxiMember
FROM source_member
GROUP BY member_id

请从url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=2d71716a4c7b04f48062892b49429d9d查看

我想你只是错过了聚合部分。试试以下命令:

;with stg_1 as (
select member_id,
max(CASE WHEN member_type = 'CarMember' THEN member_number END) AS CarMember,
max(CASE WHEN member_type = 'TaxiMember' THEN member_number END) AS TaxiMember
FROM source_member
group by member_id
)
select * FROM stg_1

请参阅此处的数据库<>提琴