How to combine / merge columns with two SQL query results?

Question

I have a dataset 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 have up to two simple PIVOT queries , one for the InputOctets column and one for the OutputOctets column (for brevity only):

 -- 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 result of the PIVOT request on InputOctets :

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

The result of the PIVOT request for OutputOctets :

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

I need to create 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 to combine two query results to create the report above?

Update:

I updated the data in the desired report format to match the data in the sample source table. I apologize for the confusion.

Answer 1

I have no idea how you calculate your HourX (In | Out) from your (Input | Output) Octets, but the following may 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 the 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 2

use union or union all to merge the two results.

Answer 3

You have two queries ... so I think you could just use these two queries as “tables” in the 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 the INNER JOIN if you want. this is pretty much the same as making two views from your queries and then joining these views.

PS: NOT TESTED ... will work with direct SQL, but I have no real experience with pivot tables, and this could be a hang

Answer 4

Create one @table variable and put all the columns in this table and paste all its values ​​into this table and finally select from this table. means that if you want 24 columns, then create a table with 24 columns and insert one row into this

Answer 5

This answer comes from Agile / YAGNI SQL Query School .....

Should the report be absolutely in this format? A simpler, more convenient request could return the correct information, it will simply be presented in a slightly different way. Does the following query return 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