How to use multi-key words in the MSSQL (ANSISQL)

Question

I know how to do in Oracle as following example code, however how can i do in ANSI SQL like MSSQL to do the same script, please help me to handle this question.

PS: this script is for multi-key words(PNO,CASENO,SCRN,IRBCODE,ITEMCODE,MMHCODE,ROWNO)

For example

SELECT * FROM TEST 
WHERE (PNO,CASENO,SCRN,IRBCODE,ITEMCODE,MMHCODE,ROWNO) 
IN 
( 
    SELECT PNO,CASENO,SCRN,IRBCODE,ITEMCODE,MMHCODE,ROWNO FROM TEST 
    WHERE CASENO > 0 AND TO_CHAR(SCRN,'YYYY') = '2017' 
)

Please let me know if my question isn't very clear

Answer 1

@Gordon Linoff had given you very great example , I just change a little bit ,hopefully it can help you

SELECT t.*
FROM irbvisit t
WHERE EXISTS (SELECT 1
              FROM irbvisit t2
              WHERE t2.CASENO > 0 AND
                  convert(varchar(4), SCRN, 126)='2017' and 
                    t2.PNO = t.PNO AND
                    t2.CASENO = t.CASENO AND
                    t2.SCRN = t.SCRN AND
                    t2.IRBCODE = t.IRBCODE AND
                    t2.ITEMCODE = t.ITEMCODE AND
                    t2.MMHCODE = t.MMHCODE AND
                    t2.ROWNO = t.ROWNO