Menu
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.
319
You need to create two separate queries and join their result not JOIN their tables. Show activity on this post. JOIN and UNION are differents. In your query you have used a CROSS JOIN operation, because when you use a comma between two table you apply a CROSS JOIN.
How do I combine two SQL query results? There are basically three ways to combine query data: joins, unions, and subqueries. Of those three, only unions and subqueries will combine query results. Here’s how: UNION combines distinct values from the result sets of two or more SELECT statements.
Now to merge them into a single table we are having 3 different methods. Method 1 (Cross Join): As you might have heard of several joins like inner join, outer join, in the same way cross join is there, which is used to form the Cartesian product of the tables without or with common columns.
1 First, execute each SELECT statement individually. 2 Second, combine result sets and remove duplicate rows to create the combined result set. 3 Third, sort the combined result set by the column specified in the ORDER BY clause.
Data in relational database tables are organized into rows and columns. As we investigate ways to combine data, keep in mind that the end result will be to either add more columns to a result, perhaps from another relate table, or rows, by taking a set of rows from two or more tables.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
