Merge Two Group By Rows SQL
You have two possibilities: PIVOT and a crosstab:

DECLARE @sampleData AS TABLE (
    SiteId int,
    SiteName varchar(50),
    EventCode int,
    EventCount int
);

INSERT INTO @sampleData VALUES 
(1,'Abbottabad',43,53),
(1,'Abbottabad',44,52);


SELECT *
FROM @sampleData
PIVOT (SUM(EventCount) FOR EventCode IN ([43],[44])) AS p;


SELECT 
    SiteId, 
    SiteName, 
    SUM(CASE EventCode WHEN 43 THEN EventCount END) AS [OpenEvent Count(Event = 43)],
    SUM(CASE EventCode WHEN 44 THEN EventCount END) AS [CloseEvent Count(Event = 44)]
FROM @sampleData
GROUP BY SiteId, 
    SiteName;


Personally I prefer a crosstab, as it is more flexible.

Edit: changed to original table to clarify the technique.

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

CREATE TABLE #MyLogs(
    SiteId int,
    SiteName varchar(50),
    EventCode int
);


-- Insert some test data
INSERT INTO #MyLogs 
SELECT 1 AS SiteId, 'Abbottabad' AS SiteName, 43 AS EventCode
FROM master.dbo.spt_values
WHERE type = 'P'
    AND number BETWEEN 1 AND 53;

INSERT INTO #MyLogs 
SELECT 1 AS SiteId, 'Abbottabad' AS SiteName, 44 AS EventCode
FROM master.dbo.spt_values
WHERE type = 'P'
    AND number BETWEEN 1 AND 52


SELECT 
    SiteId, 
    SiteName, 
    SUM(CASE EventCode WHEN 43 THEN 1 END) AS [OpenEvent Count(Event = 43)],
    SUM(CASE EventCode WHEN 44 THEN 1 END) AS [CloseEvent Count(Event = 44)]
FROM #MyLogs
GROUP BY SiteId, 
    SiteName;