Hi all,
I'm in the midst of optimizing a solution provided by my vendor, this solution is live in use already for many years. Hence I am unable to alter the database schema which is planned as below:
Table: Branch
Column: BranchName, BranchABBR
Table: StockDetails
pID, sStoreID (which is mapped directly to BranchName), s15Days
Table: Stock
pID, sOutletName(created base on branchname example: BranchName = Outlet1, a new column will be created for the branchname: sOutlet1)
Would like to ask if there is anyway I can join the 'stockdetails' table to 'stock' table via the 'sStoreID' from 'StockDetails' to the specific column that is in Stock table through purely SQL query only?
My current query is as below and i want to ideally draw the column like
c.('s' + A.BranchName) I know it would not work but I really have got no idea how do i dynamically draw the desired column from SQL itself.
SELECT *
FROM(
SELECT c.pID, A.BranchName, c.('s' + A.BranchName), b.s15Days, b.s30Days, b.s60Days
FROM Branch A
INNER JOIN StockDetails B
ON A.BranchName = B.sStoreID AND A.BranchActive = 1
INNER JOIN Stock C
ON B.pID = C.pID AND C.sDisable = 'No'
WHERE c.pID = 17705) A
As I am getting the result as below:
PID, BranchName, Outlet1, Outlet2, Outlet3, s15Days, s30Days, s60Days..
My desired results is as
PID, BranchName, Outlet1, S15days, s30Days, s60Days
PID, BranchName, Outlet2, S15days, s30Days, s60Days
PID, BranchName, Outlet3, S15days, s30Days, s60Days
and Outlet1, Outlet2, Outlet3 are columns created in the 'Stock' table, it will increase as more outlets are added. hence I will need a dynamic query to map and display the columns accordingly