Database

SELECT
    DataPoint,
    STUFF
    (
        (
            SELECT ',' + Site
            FROM YourTable As I
            WHERE I.DataPoint = O.DataPoint
            ORDER BY Site
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)')
        
        -- Remove the first comma:
        , 1, 1, ''
    ) As Sites
FROM
    YourTable As O
GROUP BY
    DataPoint
;

/*
Output:

DATAPOINT   SITES
-----------------
Power       CH
Temp        CH,KC
WindSpeed   CH,KC
*/

DECLARE @cols As nvarchar(max), @query As nvarchar(max);

SET @cols = STUFF
(
    (
        SELECT DISTINCT ',' + QUOTENAME(Site)
        FROM Source
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)')
    , 1, 1, ''
);

SET @query = N'SELECT DataPoint, ' + @cols + N' FROM Source PIVOT (COUNT(Site) FOR Site IN (' + @cols + N')) As p';

EXEC(@query);

/*
Output:

DATAPOINT    CH    KC
Power         1     0
Temp          1     1
WindSpeed     1     1
*/