For

select *

from CV

left join DeclarationResponse dr on dr.CVID = cv.CVID and dr.DeclarationQuestionID in (9, 2)

This is the result

I only need to display record if DeclarationResponseID is not null. So, the CVID was 72, 76 and 77

See below,

1- I want to make it - the DeclarationQuestionID value is a column.

2- Based on DeclarationQuestionID value, the column will be 2 and 9

Based on CVID=72 and DeclarationQuestionID=2. So, Response='' and Answer=0.

Based on CVID=72, there is no DeclarationQuestionID=9. So, Response=null and Answer=null.

So, the result must be

CVID | Answer-Staff (9) | Response-Staff Number (9) | Answer-Disease (2) | Response-Physical (2)

_____________________________________________________________________________________________________________

72 | null | null | 0 |

Based on CVID=76 and DeclarationQuestionID=2. So, Response='' and Answer=0.

Based on CVID=76 and DeclarationQuestionID=9. So, Response='' and Answer=0.

So, the result must be

CVID | Answer-Staff (9) | Response-Staff Number (9) | Answer-Disease (2) | Response-Physical (2)

_____________________________________________________________________________________________________________

76 | 0 | | 0 |

Based on CVID=77 and DeclarationQuestionID=2. So, Response='' and Answer=0.

Based on CVID=77, there is no DeclarationQuestionID=9. So, Response=null and Answer=null.

So, the result must be

CVID | Answer-Staff (9) | Response-Staff Number (9) | Answer-Disease (2) | Response-Physical (2)

_____________________________________________________________________________________________________________

77 | null | null | 0 |

The value = null must be given, if CVID do not have DeclarationQuestionID=2 nor DeclarationQuestionID=9