How to make query table with generated "dynamic" column from another table's row?

Question

These are the source tables from TBL_MATERIALS & TBL_PRODUCTS that I want to combine. The columns should be somewhat dynamic, based on how many rows are in the TBL_MATERIALS.

TBL_MATERIALS

|===============================|                               
|MATERIAL           |      Gram |                           
|-------------------------------|                              
|Flour-Hard         |      25   |               
|Flour Soft         |      76   |                 
|Sugar-White        |      25   |                      
|Sugar-Washed       |      15   |                   
|Sugar-Brown        |      10   |                      
|CalciumPropionate  |      2.5  |                                  
|SodiumBenzoate     |      2    |                              
|TartarCream        |      5    |                     
|MilkSkimmed        |      20   |                
|===============================|

TBL_PRODUCTS

|===============================|                               
|Product            |     Batch |                              
|-------------------------------|                                                 
|Ameriloaf          |     5     |                         
|Peter Pann         |     2.5   |                            
|Chizmada           |     3     |                        
|Ubemada            |     8     |                            
|Millionaire        |     9     |                      
|Sweet Maria        |     2.5   |                          
|Butter Tarts       |     1.25  |                              
|Caramel Croquette  |     4     |                                  
|Garlic Stick       |     11    |                               
|===============================|

This is what I want the table should look like. QUERY_CUSTOM's columns should be dynamic, that when I add a new item on TBL_MATERIALS, a new column for the table below should show. I think we will use something like CREATE TABLE or something. I'm still researching the thing. I hope this will help you understand.

QUERY_CUSTOM                                                                 
|=================================================================================================|
|Product            |     Batch | Flour-Hard | Flour-Soft | Sugar-White | Sugar-Washed | etc.etc. |    
|-------------------------------------------------------------------------------------------------|     
|Ameriloaf          |     5     | Gram*Batch | ALL BLANK  |             |              |          |
|Peter Pann         |     2.5   |ex.25*2.5=75| CELLS      |             |              |          |
|Chizmada           |     3     |      "     | SHOULD     |             |              |          |
|Ubemada            |     8     |      "     | BE FILLED  |             |              |          |
|Millionaire        |     9     |      "     | WITH       |             |              |          |
|Sweet Maria        |     2.5   |      "     |[GRAM]      |             |              |          |
|Butter Tarts       |     1.25  |      "     | MULTIPLIED |             |              |          |
|Caramel Croquette  |     4     |      "     | BY CORRESP.|             |              |          |
|Garlic Stick       |     11    |      "     |[BATCH]     |             |              |          |
|=================================================================================================|

Do all materials go with all products? I don't see a relationship indicating which materials go with which products — Sparky, Jun 17 '12 at 14:42
Yes all the products. I just filled the 1st one bec. but all will be filled. For example.. Row->Millionaire with a Column->Sugar-White so the formula will become: [Batch] * [Gram] so... 9 * 25 = 225 — jestrange, Jun 17 '12 at 15:41
So, all your products have same ratio of materials? They all have `Flour-Hard / TartarCream = 25 / 5 = 5.0` ?? — Damir Sudarevic, Jun 17 '12 at 16:45
I made this not logically correct, but a demo. Basically it's like mapping x and y ex. Butter Tarts & Sugar-White: So.. 1.25 * 25 = 31.05 So it's not logically justifiable or sorts, I just chose those names for recalling them (i ripped from my database). — jestrange, Jun 17 '12 at 20:57

score 0 · Answer 1 · answered Jun 17 '12 at 17:52

select
      Product
    , Batch
    , sum( case when Material = 'Flour-Hard'        then Product_Grams else 0.0 end ) as Flour_Hard
    , sum( case when Material = 'Flour-Soft'        then Product_Grams else 0.0 end ) as Flour_Soft 
    , sum( case when Material = 'Sugar-White'       then Product_Grams else 0.0 end ) as Sugar_White 
    , sum( case when Material = 'Sugar-Washed'      then Product_Grams else 0.0 end ) as Sugar_Washed
    , sum( case when Material = 'Sugar-Brown'       then Product_Grams else 0.0 end ) as Sugar_Brown
    , sum( case when Material = 'CalciumPropionate' then Product_Grams else 0.0 end ) as CalciumPropionate
    , sum( case when Material = 'SodiumBenzoate'    then Product_Grams else 0.0 end ) as SodiumBenzoate
    , sum( case when Material = 'TartarCream'       then Product_Grams else 0.0 end ) as TartarCream
    , sum( case when Material = 'MilkSkimmed'       then Product_Grams else 0.0 end ) as MilkSkimmed
from (
    select
          a.Product
        , a.Batch
        , b.Material
        , b.Gram
        , (a.Batch * b.Gram) as Product_Grams
    from       TBL_PRODUCTS  as a
    cross join TBL_MATERIALS as b
) as xx
group by Product, Batch
order by Product
;

Thanks for trying but it's not quite the answer I'm looking for can you make it dynamic that when I add a new item at TBL_Materials, a new column will show on QUERY_CUSTOM table. Thanks anyways. — jestrange, Jun 17 '12 at 20:52
@JesKeshlyCruz; I see, this can be "simply" generated with dynamic SQL, here is an example http://stackoverflow.com/questions/1914303/sql-syntax-to-pivot-multiple-tables/1915438#1915438 — Damir Sudarevic, Jun 29 '12 at 20:37

score -1 · Answer 2 · answered Jun 17 '12 at 15:58

-1

If I understand your question correctly, you need to use the formula on insertion and update. When defining the foreign key, append 'on update cascade'. This makes the referenced values update with it.

answered Jun 17 '12 at 15:58

EvenLisle

4,672
3
24
47

Unfortunately, I'm not very familiar with those very much. Can you post the code? or give an example pls. Thanks anyway. – jestrange Jun 17 '12 at 16:13

How to make query table with generated "dynamic" column from another table's row?

2 Answers2