Same ID with multiple records to single row

I have two tables.

Table A look like this.

source	ID	Type_ID	Error_info
ABC	100	1	country column missing
ABC	100	2	conversion factor missing decimals
BCA	200	1	error value in height column
BCA	200	2	convertion factor should be 0.001

Table B look like this.

source	ID	Type_1_ID	Error_info_1	Type_2_ID	Error_info_2
ABC	100
BCA	200

I want to join both these tables based on Source and ID column. If you see the table A you can see for the ID 100 and 200 i have two records but the difference is Type_ID column and Error_info column. I want both these records in a single row like the below table.

source	ID	Type_1_ID	Error_info_1	Type_2_ID	Error_info_2
ABC	100	1	country column missing	2	conversion factor missing decimals
BCA	200	1	error value in height column	2	convertion factor should be 0.001

Is there way to achieve this. I tried using case statement obviously it didn’t work. any suggestion will definitely help me.

Answer

If you just want to view the output suggested in table B using table A, then use a pivot query:

SELECT
    source,
    ID,
    1 AS Type_1_ID
    MAX(CASE WHEN Type_ID = 1 THEN Error_info END) AS Error_info_1,
    2 AS Type_2_ID,
    MAX(CASE WHEN Type_ID = 2 THEN Error_info END) AS Error_info_2
FROM yourTable
GROUP BY
    source,
    ID;