demo, Sql Server - rextester

Language: Layout:

IF OBJECT_ID('tempdb..#table') IS NOT NULL DROP TABLE #table

CREATE TABLE #table (ITEM varchar(100),   SITE  INT)
INSERT INTO #table SELECT 'A',      1
INSERT INTO #table SELECT 'A',      2
INSERT INTO #table SELECT 'A',      3
INSERT INTO #table SELECT 'B',      2
INSERT INTO #table SELECT 'B',      4
INSERT INTO #table SELECT 'C',      1
INSERT INTO #table SELECT 'C',      3
INSERT INTO #table SELECT 'C',      4
INSERT INTO #table SELECT 'C',      5

DECLARE @cols AS NVARCHAR(max) = Stuff((SELECT DISTINCT ', ' + Quotename(SITE) 
				 FROM   #table
				 FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 
		DECLARE @displayCols AS NVARCHAR(max) = 'ITEM , ' +  Stuff((SELECT DISTINCT ', case when ' + Quotename(SITE) 
							+ ' = 0 then ''No'' else ''Yes'' end  as ' + Quotename( 'SITE_' + CAST(SITE AS VARCHAR(10))) 
				 FROM   #table
				 FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @query AS NVARCHAR(max) = ' SELECT ' + @displayCols + '
											FROM   #table
												   PIVOT ( COUNT(SITE) 
														 FOR SITE IN ('+@cols+') ) pvt';

EXECUTE(@query)

View schema
Execution time: 0,05 sec, rows selected: 6, rows affected: 9, absolute service time: 0,22 sec, absolute service time: 0,21 sec