asked on

MS SQL Query

I have the below table structure...

Table1            Table2
ID            ID      ERRCODE
1            1      107
2            1      2
3            2      2
4            3      107

...and would like a query that returns the following results:
ID      CODE1      CODE2
1      2      107
2      2      NULL
3      NULL      107

Thank you in advance!

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

scuttlebuttin

ASKER

there are more error codes but i am only concerened with 2 and 107.

sorry that i didn't mention that table1 has a decscription field that i need to include; therefore a join between table1 and table2 is necessary.

so actually this is more representative of my problem:
Table1            Table2
ID      DESC      ID      ERRCODE
1      Test1      1      107
2      Test2      1      2
3      Test3      2      2
4      Test4      3

Desired Results:
DESC      CODE1      CODE2
Test1      2      107
Test2      2      NULL
Test3      NULL      107

scuttlebuttin

ASKER

i see where you are going AngelIII and will mess around with the syntax you suggested.

Guy Hengel [angelIII / a3]

ok:

SELECT T1.ID, T2.DESCRIPTION
, MAX(CASE WHEN T2.ERRCODE = 2 THEN T2.ERRCODE END) CODE1
, MAX(CASE WHEN T2.ERRCODE = 107 THEN T2.ERRCODE END) CODE2
FROM Table2 T2
JOIN Table1 T1
  ON T1.ID = t2.ID
WHERE T2.ERRORCODE IN (2,107)
GROUP BY T1.ID, T1.DESCRIPTION

Open in new window