Hello!

I know you guys may have already answered a similar question, but I was unable to search my way to an answer on this forum, so please bear with me here

I have 3 tables, structured as follows:

Table 1: Products Product Code - PK Product Description

Table 2: Stores Store Code - PK Store Description

Table 3: StoredItems Product Code - PK Store Code - PK Quantity Stored

Where 'Table 3' is the bridge table in a many-to-many relationship. 'Table 3' also holds the quantities of products stored in each store. Here is a sample of the data stored in this table:

ProdCode - StoreCode - Qty Item A - Store 1 - 10 Item A - Store 2 - 15 Item A - Store 3 - 12 Item B - Store 1 - 8 Item B - Store 2 - 7 Item B - Store 3 - 9 Item C - Store 1 - 11 Item C - Store 2 - 19 Item C - Store 3 - 18

Now, I would like to display read-only information in a grid as follows:

ProdCode - Store 1 - Store 2 - Store 3 Item A - 10 - 15 - 12 Item B - 8 - 7 - 9 Item C - 11 - 19 - 18

Now this seems easy enough using a PIVOT (or CASE) statement in a stored procedure or view, but this is NOT where my dilemma is. My problem is when I add a new 'Store' then I would need to add a new column in my query. How would I dynamically represent that using LLBL?

I hope I made myself clear.

Thanks in advance.

I would do as follows:

1- Fetch all stores available (StoreCode & Name if available) 2- Foreach Store in the previously fetched stores, do the following: 2.1- Use a DynamicList to fetch the stored Products & their Quantities. 3- Now you should have different resultSets (dataTables), one for each Store. 4- Dynamically in code merge those dataTables into one dataTable. (that's the hardest part )

Thank you very much for you prompt response.

Walaa wrote:

2.1- Use a DynamicList to fetch the stored Products & their Quantities.

Why would I need to do this? I already have a table (Table 3) that includes this information; or am I missing something here.

Walaa wrote:

4- Dynamically in code merge those dataTables into one dataTable. (that's the hardest part )

How would I do that?

A simple sample would help greatly!

Here's the SQL statement I want to imitate:

Select ProductCode, Sum([Store 1]) As 'Store1', Sum([Store 2]) As 'Store2', Sum([Store 3]) As 'Store3'
From (
    Select ii.ProductCode,
    'Store 1' = Case ist.StoreCode When 'WRH01' Then ist.Qty Else NULL End,
    'Store 2' = Case ist.StoreCode When 'WRH02' Then ist.Qty Else NULL End,
    'Store 3' = Case ist.StoreCode When 'WRH03' Then ist.Qty Else NULL End
    From InventoryItem ii Inner Join InventoryStock ist On ii.ProductCode = ist.ProductCode
) As TableA
Group By ProductCode

Or, using the new PIVOT way:

Select ProductCode, Sum([Store1]) As 'Store1', Sum([Store2]) As 'Store2', Sum([Store3]) As 'Store3'
From (
    Select ProductCode, [WRH01] AS Store1, [WRH01] AS Store2, [WRH01] AS Store3
    From 
        (Select * From InventoryStock) p
    PIVOT
        (SUM (decQty) FOR StoreCode IN ([WRH01], [WRH01], [WRH01])) AS pvt
) As TableA
Group By ProductCode

Hope this helps

you could also use sub-select statements (v2.x)

select ProductDescription,
          (   select   coalesce(sum(i.quantity), 0) 
              from   StoredItems i
              where  i.StoreCode = 'Store 1'
               and   i.ProductCode = ProductCode) as Store 1,
          (   select   coalesce(sum(i.quantity), 0)
              from   StoredItems i
              where  i.StoreCode = 'Store 2'
               and   i.ProductCode = ProductCode) as Store 2,
          (   select   coalesce(sum(i.quantity), 0) 
              from   StoredItems i
              where  i.StoreCode = 'Store 3'
               and   i.ProductCode = ProductCode) as Store 3,
from   Products
order by ProductDescription

this can be accomplished using scalar queries for the sub-selects. if you have a dynamic number of stores you could fetch a list of stores 1st then cycle through each store and create the sub-select field. add each field to a resultset, and fetch the dynamic list

Are you married? Cuz my sisters available!

Works perfectly! Thank you very very much! I'll be blogging the solution soon.