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

use union or union all to merge the 2 results.

Answer 2

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)

Answer 3

This answer comes from the Agile / YAGNI school of SQL queries.....

Does the report absolutely have to be in this format? A simpler, more maintainable query could return the correct information, it would just be presented slightly differently. The following query returns the data in a slightly different format?

SELECT serviceid, hour, SUM(InputOctets) AS InputOctets, SUM(OutputOctets) AS OutputOctets
FROM BWHourlyReadings
GROUP BY serviceid, hour
ORDER BY serviceid, hour

Answer 4

Create one @table variable and put all the columns in that table and insert ithe all values in that table and at last select from that table. means if you want 24 column then create table with 24 column and insert one by one row in this

Answer 5

well you have two queries... so I would think you could just use those two queries as the "tables" in a wrapper query and join them

select * from 
(*insert your big-ass OutputOctets query SQL here*) oo,
(*insert your big-ass InputOctets query SQL here*) io 
where oo.ServiceID = oi.ServiceID 

or use INNER JOIN if you prefer. this is pretty much the same as making two views out of your queries, and then joining those views.

PS: NOT TESTED ... would work with straight SQL, but I have no real experience with pivot tables and that could be the hang-up