Histogram - How to get pivot in SQL Query showing same ACN from 3 different archiveID

select distinct A.Field1 as ACN,

(select count(Field1) from ssfields where ArchiveID = '1' and Field1=A.Field1) as Row_Count_1,

(select count(Field1) from ssfields where ArchiveID = '5' and Field1=A.Field1) as Row_Count_5,

(select count(Field1) from ssfields where ArchiveID = '8' and Field1=A.Field1) as Row_Count_8

from ssfields A

SQL Script NOT IN with multiple fields

SELECT COUNT(*)

FROM dbo.ssFields

WHERE field7='Legal'

AND  ArchiveID =  '1' --Travel Agency

AND (Field1 + ' ' + Field3 + ' ' + CONVERT(VARCHAR,Field5, 23) + ' ' + field12 + ' ' + field18) NOT IN(

       SELECT (Field1 + ' ' + Field3 + ' ' + CONVERT(VARCHAR,Field5, 23) + ' ' + field12 + ' ' + field18) AS CompareString

       FROM dbo.ssFields

       WHERE field7='Legal'

       AND  ArchiveID =  '8' --Travel Agency - Date Fix

)

SQL script to compare what is NOT IN...

SELECT COUNT(*)

FROM dbo.ssFields

WHERE field7='legal'

AND  ArchiveID =  '1' --Travel Agency

AND (Field1) NOT IN(

       SELECT (Field1) AS CompareString

       FROM dbo.ssFields

       WHERE field7='legal'

       AND  ArchiveID =  '8' --Travel Agency - Date Fix

)