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.

Solution 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)

Solution 2

use union or union all to merge the 2 results.

Solution 3

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

Comments

[Kev]

That will just give me a combined result, one after the other. I need to merge two column sets together across the result.

[Kev]

The HourX(In|Out) come from the PIVOT query so that I get hours as the column names. We can only produce a separate result using PIVOT for the InputOctets and then the OutputOctets.

[Lieven Keersmaekers]

@Kev, I think you misunderstood me. The query should produce the columns you need. I just did not know how you get from [InputOctet] 383088 & [OutputOctet] 804249 to [Hour1In] 350230878.

[Kev]

Apologies Lieven, I've updated the data and re-jigged the question a bit to hopefully make it clearer.

[Lieven Keersmaekers]

@Kev, in that case, have you tried the query? You might be in for a surprise (unless "I" misunderstood offcourse).

[Kev]

Ah...you changed your query...I was puzzled by the original one...I'll give it a go. Cheers.

[an phu]

This solution will work but Lieven's is more performant and readable.