Hello again,

I managed to find a simple solution that seems working on sample tables.

;WITH Final_TB AS

(

SELECT X.SAMPLE_ID, X.AntiBio, #TestTb1.P1, #TestTb1.P2, #TestTb1.P3, #TestTb1.P4 FROM

(

SELECT [Sample_ID],'AB1' AS [AntiBio]

FROM #TestTb2 WHERE [AB1] = '3'

UNION ALL

SELECT [Sample_ID],'AB2' AS [AntiBio]

FROM #TestTb2 WHERE [AB2] = '3'

UNION ALL

SELECT [Sample_ID],'AB3' AS [AntiBio]

FROM #TestTb2 WHERE [AB3] = '3'

UNION ALL

SELECT [Sample_ID],'AB4' AS [AntiBio]

FROM #TestTb2 WHERE [AB4] = '3'

) X JOIN #TestTb1 ON X.[Sample_ID] = #TestTb1.[Sample_ID]

)

SELECT AntiBio, PCR, COUNT(1) AS [Freq.]

FROM

(

SELECT [Sample_ID], [AntiBio], 'P1' AS [PCR]

FROM Final_TB WHERE [P1] = '1'

UNION ALL

SELECT [Sample_ID], [AntiBio], 'P2' AS [PCR]

FROM Final_TB WHERE [P2] = '1'

UNION ALL

SELECT [Sample_ID], [AntiBio], 'P3' AS [PCR]

FROM Final_TB WHERE [P3] = '1'

UNION ALL

SELECT [Sample_ID], [AntiBio], 'P4' AS [PCR]

FROM Final_TB WHERE [P4] = '1'

) Y

GROUP BY Antibio, PCR ORDER BY Antibio, PCR

It returns the expected table. However, as I mentioned with too many variables (the variable numbers are dynamic), I search for a more flexible solution.

Any suggestions ?

Thanks in advance.