How do I combine/merge columns from two SQL query results?

Question

I have a set of data in a table named BWHourlyReadings, for example:

ServiceID      Hour   InputOctets    OutputOctets
=========      ====   ===========    =================
27222          1      383088         804249
27222          2      270529         688683
27222          3      247251         290124
... up to 24 hours of data
27222          24     236053         239165

28900          1      883011         914249
28900          3      444251         891124
... up to 24 hours of data
28900          24     123053         452165

For each day there are up to 24 readings per ServiceID.

I've got as far as two separate PIVOT queries, one for the InputOctets column and one for the OutputOctets column (only one shown here for brevity):

-- Replace HourXIn with HourXOut for OutputOctets
SELECT ServiceID, [1] AS 'Hour1In', [2] AS 'Hour2In', [3] AS 'Hour3In', ...
FROM
(
    SELECT 
        ServiceID,
        Hour, 
        TotalInputOctets -- Other query has OutputOctets here instead
    FROM
        BWHourlyReadings

) AS bw
PIVOT 
( 
    MAX(TotalInputOctets)  -- Other query has OutputOctets here instead
    FOR [Hour] IN ([1], [2], [3], ... [24])
) AS pvt

This gives me my InputOctets and OutputOctets in two separate result sets, for example:

The PIVOT query result on InputOctets:

ServiceID Hour1In Hour2In Hour3In . Hour24In     
========= ======= ======= =======   ========    
27222     383088  270529  247251    236053   
28900     883011  0       444251    123053   

The PIVOT query result on OutputOctets:

ServiceID Hour1Out Hour2Out Hour3Out .. Hour24Out    
========= ======== ======== ========    ========   
27222     804249   688683   290124      239165  
28900     914249   0        891124      452165

I need to produce a report like this:

ServiceID Hour1In Hour1Out Hour2In Hour2Out Hour3In Hour3Out .. Hour24In Hour24Out    
========= ======= ======== ======= ======== ======= ========    =======  ========   
27222     383088  804249   270529  688683   247251  290124      236053   239165  
28900     883011  914249   0       0        444251  891124      123053   452165

How do I merge the two query results to produce the report above?

Update:

I've updated the data in the desired report format to match the data in the source table example. My apologies for the confusion.

Answer 1

I have no idea how you calculate your HourX(In|Out) from your (Input|Output)Octets but following might work for you

SELECT 
    ServiceID
    , [Hour1In] = SUM(CASE WHEN Hour = 1 THEN InputOctets ELSE 0 END)
    , [Hour1Out] = SUM(CASE WHEN Hour = 1 THEN OutputOctets ELSE 0 END)
    , [Hour2In] = SUM(CASE WHEN Hour = 2 THEN InputOctets ELSE 0 END)
    , [Hour2Out] = SUM(CASE WHEN Hour = 2 THEN OutputOctets ELSE 0 END)
    , [Hour3In] = SUM(CASE WHEN Hour = 3 THEN InputOctets ELSE 0 END)
    , [Hour3Out] = SUM(CASE WHEN Hour = 3 THEN OutputOctets ELSE 0 END)
    -- , ...
    , [Hour24In] = SUM(CASE WHEN Hour = 24 THEN InputOctets ELSE 0 END)
    , [Hour24Out] = SUM(CASE WHEN Hour = 24 THEN OutputOctets ELSE 0 END)
FROM 
    @BWHourlyReadings
GROUP BY 
    ServiceID

Tested with following data.

DECLARE @BWHourlyReadings TABLE (ServiceID INT, Hour INT, InputOctets INTEGER, OutputOctets INTEGER)

INSERT INTO @BWHourlyReadings VALUES (27222,  1, 383088, 804249)
INSERT INTO @BWHourlyReadings VALUES (27222,  2, 270529, 688683)
INSERT INTO @BWHourlyReadings VALUES (27222,  3, 247251, 290124)
INSERT INTO @BWHourlyReadings VALUES (27222, 24, 236053, 239165)