我試圖從ID中獲取該行的特定值，并從這些行列中給它一個選擇的結果。
例如，我有一個這樣的表格： 我需要將其轉換為表格，像這樣： 我曾嘗試使用像這樣的腳本，但沒有任何幫助 uj5u.com熱心網友回復： 這里不需要CTE。只需使用聚合函式來檢索所需的結果。 Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=2d71716a4c7b04f48062892b49429d9d uj5u.com熱心網友回復： 我認為你的嘗試只是漏掉了聚合的部分。
試試下面的方法： 請看db<>fiddle 這里。
標籤：
member_id
member_number 會員型別
1511
99084213654
CarMember
1511 1511
4435654121 4435654121?
TaxiMember TaxiMember
1511 1511
AA11133 AA11133
BusMember
BusMember
6612?
97970897131?
CarMember CarMember
6612
AA23564 AA23564
BusMember
BusMember
member_id
汽車會員 TaxiMember
1511 990842136544435654121
6612
97970897131?
NULL
6612 97970897131 NULL
with stg_1 as (
select member_id,
CASE WHEN member_type = 'CarMember' THEN member_number END as CarMember,
CASE WHEN member_type = 'TaxiMember' THEN member_number END As TaxiMember
FROM source_member
)
select * FROM
-- SQL Server (v2017)
SELECT member_id
, MAX(CASE WHEN member_type= 'CarMember' THEN member_number END) 委員
, MAX(CASE WHEN member_type = 'TaxiMember' THEN member_number END) 出租車成員
FROM source_member
GROUP BY member_id
;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