I have two tables.

Table A look like this.

Table B look like this.

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.

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

CodePudding user response：

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;

CodePudding user response：

your data

declare @a table(
  source VARCHAR(100) NOT NULL, 
  ID INTEGER NOT NULL, 
  Type_ID INTEGER NOT NULL, 
  Error_info VARCHAR(max) NOT NULL
);
INSERT INTO @a(source, ID, Type_ID, Error_info) 
VALUES 
  (
    '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'
  );

using multiple pivot as follows

SELECT 
  source, 
  ID, 
  max(Type_1_ID) Type_1_ID, 
  max(Error_info_1) Error_info_1, 
  max(Type_2_ID) Type_2_ID, 
  max(Error_info_2) Error_info_2 
FROM 
  (
    SELECT 
      source, 
      ID, 
      Type_ID, 
      CONCAT('Error_info_', Type_ID) AS Col1, 
      CONCAT('Type_', Type_ID, '_ID') AS Col2, 
      Error_info 
    FROM 
      @a
  ) Src PIVOT (
    MAX(Error_info) FOR Col1 IN ([Error_info_1], [Error_info_2])
  ) Pvt1 PIVOT (
    max(Type_ID) FOR Col2 IN ([Type_1_ID], [Type_2_ID])
  ) AS pv2 
group by 
  source, 
  ID