Combining Data From Temp Tables
-
I deleted my last question since I feel like I've made some kind of progress. However, I'm still not sure how to group all of the data together. I have five tables that hold data. What I am doing is selecting: UserId, ProductNum, Qty into a temporary table for each master data table. Here's an example of what each temp table looks like:
John PART-NUM-ONE 5
Jane PART-NUM-ONE 8
Jane PART-NUM-TWO 4
Jack PART-NUM-THREE 9I have five tables structured just like that, except the Part Number and Qty will be different in each table. How can I build a query to group by Name, then by Part Number, and a column for each temp table (e.g. Passed, Finished, Scrapped)? I just can't seem to combine them. The Part Numbers come from a Parts table, which stores the Part Number.
djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.
I came a bit late to the problem. Can you confirm that in your example there are 4 temporary tables? Is this going to be static? (Always 4 Users) The solution appears to be using a series of Select statements with UNION.
-
I came a bit late to the problem. Can you confirm that in your example there are 4 temporary tables? Is this going to be static? (Always 4 Users) The solution appears to be using a series of Select statements with UNION.
Well, I forgot to elaborate the end result I am going for. It is not a limit of users. The temp tables store every user's numbers for a particular piece of the process (Pass, Finished, Scrap, etc.). The end result I'm looking for is like this:
Name Part Passed Finished Scrap
John P-ONE 8 5 7
Jane P-ONE 4 6 10
Jane P-TWO 9 2 3
Jack P-THREE 10 12 6Bring in the data from ALL temp tables into a group of columns, I guess sort of like a pivot. However, I can't seem to implement any kind of pivot in SQL that solves the problem.
djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.
-
Well, I forgot to elaborate the end result I am going for. It is not a limit of users. The temp tables store every user's numbers for a particular piece of the process (Pass, Finished, Scrap, etc.). The end result I'm looking for is like this:
Name Part Passed Finished Scrap
John P-ONE 8 5 7
Jane P-ONE 4 6 10
Jane P-TWO 9 2 3
Jack P-THREE 10 12 6Bring in the data from ALL temp tables into a group of columns, I guess sort of like a pivot. However, I can't seem to implement any kind of pivot in SQL that solves the problem.
djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.
I would use a pivot
SELECT UserId,ProductNum,Passed,Finished,Scrapped
FROM
(SELECT UserId, ProductNum, Qty, 'passed' as status
FROM Passed
UNION
SELECT UserId, ProductNum, Qty, 'finished' as status
FROM finished
UNION
SELECT UserId, ProductNum, Qty, 'scrapped' as status
FROM scrapped
) AS SourceTable
PIVOT
(
sum(qty)
FOR status IN ('passed', 'finished','scrapped')
) AS PivotTable;I havent tested this, but the principle should work. I also have the opinion that you should normalize the database, several tables with the same content isn't good. Put them together into one table with a status column.
People say nothing is impossible, but I do nothing every day.
-
Well, I forgot to elaborate the end result I am going for. It is not a limit of users. The temp tables store every user's numbers for a particular piece of the process (Pass, Finished, Scrap, etc.). The end result I'm looking for is like this:
Name Part Passed Finished Scrap
John P-ONE 8 5 7
Jane P-ONE 4 6 10
Jane P-TWO 9 2 3
Jack P-THREE 10 12 6Bring in the data from ALL temp tables into a group of columns, I guess sort of like a pivot. However, I can't seem to implement any kind of pivot in SQL that solves the problem.
djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.
I agree with Jorgen, Pivot is you best option, and the database should be normalised, the design is rubbish! Alternatively you could do this:
Select Name, Part, 1 Passed,0 Finished,0 Scrap
from PassedTable
UNION
Select Name, Part, 0 Passed,1 Finished,0 Scrap
from FinishedTable
UNION
Select Name, Part, 0 Passed,0 Finished,1 Scrap
from ScrapTableGroup and sum the results as required.
Never underestimate the power of human stupidity RAH
-
I would use a pivot
SELECT UserId,ProductNum,Passed,Finished,Scrapped
FROM
(SELECT UserId, ProductNum, Qty, 'passed' as status
FROM Passed
UNION
SELECT UserId, ProductNum, Qty, 'finished' as status
FROM finished
UNION
SELECT UserId, ProductNum, Qty, 'scrapped' as status
FROM scrapped
) AS SourceTable
PIVOT
(
sum(qty)
FOR status IN ('passed', 'finished','scrapped')
) AS PivotTable;I havent tested this, but the principle should work. I also have the opinion that you should normalize the database, several tables with the same content isn't good. Put them together into one table with a status column.
People say nothing is impossible, but I do nothing every day.
-
I agree with Jorgen, Pivot is you best option, and the database should be normalised, the design is rubbish! Alternatively you could do this:
Select Name, Part, 1 Passed,0 Finished,0 Scrap
from PassedTable
UNION
Select Name, Part, 0 Passed,1 Finished,0 Scrap
from FinishedTable
UNION
Select Name, Part, 0 Passed,0 Finished,1 Scrap
from ScrapTableGroup and sum the results as required.
Never underestimate the power of human stupidity RAH
How is it not normalized? We must keep up with each movement for historical purposes. The temp tables only contain the same type of data because I cannot figure out how to make it work with the core tables.
djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.
-
I agree with Jorgen, Pivot is you best option, and the database should be normalised, the design is rubbish! Alternatively you could do this:
Select Name, Part, 1 Passed,0 Finished,0 Scrap
from PassedTable
UNION
Select Name, Part, 0 Passed,1 Finished,0 Scrap
from FinishedTable
UNION
Select Name, Part, 0 Passed,0 Finished,1 Scrap
from ScrapTableGroup and sum the results as required.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
Select Name, Part, 1 Qty Passed,0 Finished,0 Scrap
from PassedTable
UNION
Select Name, Part, 0 Passed,1 Qty Finished,0 Scrap
from FinishedTable
UNION
Select Name, Part, 0 Passed,0 Finished,1 Qty Scrap
from ScrapTableFTFY This is probably more efficient than a normal Pivot. I'd use your suggestion instead.
People say nothing is impossible, but I do nothing every day.
-
How is it not normalized? We must keep up with each movement for historical purposes. The temp tables only contain the same type of data because I cannot figure out how to make it work with the core tables.
djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.
Matt U. wrote:
How is it not normalized
Because you have five tables using the same columns and key Consider the following design instead:
UserId ProductNum Status QtyWhere status is Passed, Finished, Scrapped and so on. (Or rather a reference from a status table.) And if you need just the scrapped products you could use a view instead of a table for that purpose.
CREATE VIEW Finished AS
SELECT UserId,ProductNum,Qty
FROM mytable
WHERE status = 'finished'How does the core tables look like if they present such a problem?
People say nothing is impossible, but I do nothing every day.
-
Matt U. wrote:
How is it not normalized
Because you have five tables using the same columns and key Consider the following design instead:
UserId ProductNum Status QtyWhere status is Passed, Finished, Scrapped and so on. (Or rather a reference from a status table.) And if you need just the scrapped products you could use a view instead of a table for that purpose.
CREATE VIEW Finished AS
SELECT UserId,ProductNum,Qty
FROM mytable
WHERE status = 'finished'How does the core tables look like if they present such a problem?
People say nothing is impossible, but I do nothing every day.
Well, I implemented the UNION suggestion and it works perfectly. And thanks to that solution I should not have any problem using the same concept with the raw tables. It wasn't the fact that I don't have any structure to it (but I don't claim to be an expert DBA or anything ;P). I've only had experience with joins, not unions and pivots and such. I appreciate all of the suggestions and input. I've got it working exactly how I needed it to. Thanks again, Jorgen and Mycroft. :)
djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.
-
Well, I implemented the UNION suggestion and it works perfectly. And thanks to that solution I should not have any problem using the same concept with the raw tables. It wasn't the fact that I don't have any structure to it (but I don't claim to be an expert DBA or anything ;P). I've only had experience with joins, not unions and pivots and such. I appreciate all of the suggestions and input. I've got it working exactly how I needed it to. Thanks again, Jorgen and Mycroft. :)
djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.
You're welcome.
People say nothing is impossible, but I do nothing every day.