I am trying to get the specific value of row with that refer from the ID, and give it a result as selected from these column of row.

For example, I have a table like this:

I need to convert it into a table like this:

I have trying using a script like this, but didn't help

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

CodePudding user response：

CTE is not needed here. Just use aggregate function for retrieving desired result.

-- 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

Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=2d71716a4c7b04f48062892b49429d9d

CodePudding user response：

I think you only missed the aggregation part in your attempt. Try the following:

;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

Please see the db<>fiddle here.