How Can I Combine Multiple Tables with Different Structures into a Single Unified Output Table Using SQL?

Combining Tables for a Unified Output

Tables can be merged to achieve various data manipulation outcomes. One common task is to consolidate data from multiple tables into a single table for further analysis. Consider the following scenario:

You have two tables, KnownHours and UnknownHours, containing hours data associated with charge numbers. Your objective is to group these hours, regardless of month, into a single table with the following format:

ChargeNum    CategoryID     Hours
111111       1              90
111111       2              50
111111       Unknown        110.5
222222       1              40
222222       Unknown        25.5

In this scenario, you need to aggregate the hours for each unique combination of ChargeNum and CategoryID (or "Unknown" for UnknownHours). To achieve this, you can leverage the UNION operator:

SELECT ChargeNum, CategoryID, SUM(Hours)
FROM KnownHours
GROUP BY ChargeNum, CategoryID
UNION ALL
SELECT ChargeNum, 'Unknown' AS CategoryID, SUM(Hours)
FROM UnknownHours
GROUP BY ChargeNum

This SQL statement combines the results of two queries. The first query groups and sums the hours from the KnownHours table by ChargeNum and CategoryID. The second query groups and sums the hours from the UnknownHours table by ChargeNum and assigns the CategoryID 'Unknown'.

By combining the results using UNION ALL, the final table includes rows from both queries. The SUM() aggregate function ensures that hours are accumulated for matching ChargeNums and CategoryIDs.

Using this technique, you can efficiently merge tables to create a consolidated view of your data.

The above is the detailed content of How Can I Combine Multiple Tables with Different Structures into a Single Unified Output Table Using SQL?. For more information, please follow other related articles on the PHP Chinese website!