Merge Two Group By Rows SQL How do I merge two rows returned by a Group By statement:

    Select  SiteId, 
            SiteName, 
            EventCount = Count(*)
        From #MyLogs

        GROUP BY EventCode, SiteId, SiteName


returns this:

SiteId   SiteName       EventCount
 1      Abbottabad         53
 1      Abbottabad         52


How do I merge these results into one Row

The result I need is:

    SiteId   SiteName  |  OpenEvent            |        CloseEvent
                       | Count(Event = 43)     |     Count(Event = 44)
---------------------------------------------------------------------
      1     Abbottabad |  53                   |          52


I have posted the sample data and query here: Link
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;