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.

Lieven Keersmaekers · Accepted Answer · 2009-06-29 15:18:02Z

3

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)

edited Jun 29, 2009 at 15:18

answered Jun 29, 2009 at 12:00

Lieven Keersmaekers

58.9k15 gold badges117 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kev Over a year ago

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 Over a year ago

@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 Over a year ago

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

Lieven Keersmaekers Over a year ago

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

Kev Over a year ago

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

Mladen Prajdic · Accepted Answer · 2009-06-29 11:38:31Z

2

use union or union all to merge the 2 results.

answered Jun 29, 2009 at 11:38

Mladen Prajdic

15.7k2 gold badges46 silver badges51 bronze badges

1 Comment

Kev Over a year ago

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

Nicholas Franceschina · Accepted Answer · 2009-06-29 17:05:53Z

1

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

answered Jun 29, 2009 at 17:05

Nicholas Franceschina

6,1976 gold badges41 silver badges52 bronze badges

1 Comment

an phu Over a year ago

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

KuldipMCA · Accepted Answer · 2009-06-29 14:05:52Z

0

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

answered Jun 29, 2009 at 14:05

KuldipMCA

3,1617 gold badges29 silver badges48 bronze badges

Comments

Gais · Accepted Answer · 2009-06-29 13:21:23Z

-1

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

answered Jun 29, 2009 at 13:21

Gais

5753 gold badges10 silver badges17 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

5 Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related