Is It Possible - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Is It Possible

Author

Topic

sturner333
Starting Member

22 Posts

Posted - 2009-04-16 : 12:04:37

I have a project table and a Bill of Material table. The BOM is related to the project table in that a BOM record will have a column that has the id of the project that record belongs to. Each BOM record also has a column that shows what division the part belongs in.

The report or query I need from this is:
add up all records in the BOM for each division and group those divions according to which project they belong to. It would look like this:

ProjectNumber1 division1_Total division2_Total division3_Total
ProjectNumber2 division1_Total division2_Total division3_Total
ProjectNumber3 division1_Total division2_Total division3_Total

Any help would be appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 13:37:56

does a project always have only a maximum of 3 divisions? or do you always want only totals of 3 divisions per project regardless of number of divisions which is actually present?
if you want to dynamically determine number of divisions you need below

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

sturner333
Starting Member

22 Posts

Posted - 2009-04-16 : 17:42:38

There are always 12 divisions. All projects could have BOM for those 12 divisions.The number of projects are always changing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 10:07:12

so you want total for each of 12 division for each project?


SELECT ProjectNumber,
SUM(CASE WHEN Division='division1' THEN BOMAmount ELSE 0 END) AS division1_Total,
SUM(CASE WHEN Division='division2' THEN BOMAmount ELSE 0 END) AS division2_Total,
SUM(CASE WHEN Division='division3' THEN BOMAmount ELSE 0 END) AS division3_Total,
SUM(CASE WHEN Division='division4' THEN BOMAmount ELSE 0 END) AS division4_Total,
....
SUM(CASE WHEN Division='division12' THEN BOMAmount ELSE 0 END) AS division12_Total
FROM BOMTable
GROUP BY ProjectNumber

sturner333
Starting Member

22 Posts

Posted - 2009-04-17 : 12:02:16

Thanks for the input (first response visakh16). That is a great way of doing what I need. I learn something new everyday with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 13:41:18

you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources