CODEWITHSUNDEEP
c#asp.net-mvcvisual-studio-2013sql-server-2012
Wairimu Murigi
Wairimu Murigi

Reputation: 2267

Create an ordered table

I am not sure if I titled this question correctly, here is my question. I have a table that has Products and these products have various details including buying price, selling price, initial stock quantity, number of items sold, number of items remaining. I then have another table with sales information based on location of the buyer lets call it LocationSales. I need to create a table that will show the products and the location information like in the snapshot below. Graphical representation of what I want to achieve

I made the representation using Excel. I had already achieved this while working on a project earlier last year but it involved a lot or hard-coding and stack-overflow exceptions. I would like to achieve this more effectively. This is a hypothetical representation of my actual problem. I have tried using hierarchical tables before using Telerik UI which worked well but did not display the information quite as required by my superiors. I would really appreciate it if someone would point me to the right direction. I understand it's impossible to address the entire problem here but I would appreciate someone including a link to a blog post or video or some literature that can assist me. PS. I have tried database views as well, didn't work as required.

I am using ASP.NET C# MVC5 with Visual Studio 2013 and the database as SQL Server 2012

EDIT://Tables I am using

enter image description here

EDIT 2:/// As per my research so far i have managed to obtain the following

enter image description here

SQL CODE://

select * from
(select
locationName
,productName as [PRODUCT]
,roadTransfer
,airTransfer
,initialQty
,soldQty
,remQty
from
Location as l inner join
Sales as s on l.locationID=s.locationID
inner join 
Product as p on p.productID=s.productID
)as BaseData
pivot(
count(roadTransfer) 
for locationName
in([Germany]
    ,[Kenya]
) as SummaryTable
order by Product asc

I used a pivot table. I am currently trying to get both the road and air transfer. I I will try using a GROUP BY clause and see what I can do. I will post an update when I have the full solution.

Upvotes: 0

Views: 157

Answers (1)

sarin
sarin

Reputation: 5307

So at this point (Edit 2) you have created a pivot for road transfer. Unfortunately you can't aggregate more than one value in a pivot, so the way round it is to do two pivots and join the results together. You result set therefore has 3 query parts to produce the query

  1. A list of products
  2. Pivoted Road Transfer amounts by country
  3. Pivoted Air Transfer amounts by country

If you left join all of them together in a single query on the product ID you will get close to your query, but the column order won't be quite right i.e. all the Road Transfers will come before the Air Transfers.

I have created a SQLFiddle demonstrating this

Select p.prodName AS Product,
  p.initialQty AS [Initial Quantity], 
  p.sold AS [Quantity Sold], 
  p.remaining AS [Quantity Remaining], 
  AirT.[Air Transfer - Germany],
  RoadT.[Road Transfer - Germany],
  AirT.[Air Transfer - Kenya],
  RoadT.[Road Transfer - Kenya]

FROM Products p
LEFT JOIN ( 
            SELECT productID, [Germany] AS [Air Transfer - Germany], [Kenya] AS [Air Transfer - Kenya]
            FROM (select productID, l.locName, qty 
                  FROM sales s
                  INNER JOIN Locations l on l.locID = s.locID
                  where transfer = 'Air Transfer') as SourceTable
            PIVOT (SUM(qty)
            FOR locName IN ([Germany], [Kenya])
                   ) As AirTransfers
          ) AirT on AirT.productID = p.ProductID

LEFT JOIN (           
            SELECT productID, [Germany] AS [Road Transfer - Germany], [Kenya] AS [Road Transfer - Kenya]
            FROM (select productID, l.locName, qty 
                  FROM sales s
                  INNER JOIN Locations l on l.locID = s.locID
                  where transfer = 'Road Transfer') as SourceTable
            PIVOT (SUM(qty)
            FOR locName IN ([Germany], [Kenya])
                   ) As RoadTransfers
          ) RoadT on RoadT.productID = p.ProductID

Upvotes: 1

Related Questions