SQL able to join row data to column?

Question

0.00/5 (No votes)

See more:

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.

SQL

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

Posted 17-Jun-13 15:18pm

saberbladez

Updated 17-Jun-13 16:38pm

v3

Add a Solution

Comments

chaau 17-Jun-13 22:03pm

It is not really clear what you have asked

saberbladez 17-Jun-13 22:39pm

Hello I have ammended my question maybe it will be clearer now :)

Alexander andri 17-Jun-13 22:48pm

You can use union like:

select PID, BranchName, Outlet1, s15Days, s30Days, s60Days
union
select PID, BranchName, Outlet2, s15Days, s30Days, s60Days
union
select PID, BranchName, Outlet3, s15Days, s30Days, s60Days

saberbladez 17-Jun-13 22:52pm

yea thought of that, but it's causing some performance issue, it's taking 3-4s for 1 query to be done, i am thinking of there is an alternative for this as we will be querying for 30outlets for now, in time to come maybe there will be 50outlets which will be another issue

cigwork 18-Jun-13 15:07pm

What is the purpose of the "sOutlet" columns in the stock table? I expect I have misunderstood something, but they don't seem to add anything to the structure you've outlined. If they are just cruft then I'd expect to be able to use something like this...

select
stockdetail.PID
, branch.[name]
, stockdetail.S15
, stockdetail.S30
from [stockdetail]
inner join [branch]
on [branch].[name] = [stockdetail].[storeID]
inner join [stock]
on [stock].[PID] = [stockdetail].[PID]
and [stock].[disable] = 0

saberbladez 18-Jun-13 21:30pm

Hi cigwork,

The 'sOutlet' is a "dynamic column" which means that each time a new outlet is opened, there will be a new column. Example: existing outlets are sOutlet1, sOutlet2, sOutlet3. so if there is a new outlet opened, a new column will be created for the outlet, being sOutlet4. hence I am having the issue of mapping my row data to the columns itself

Maciej Los 30-Jun-13 10:46am

Still not clear. I think you're trying to create dynamic rows not columns based on one column values. For example, if BranchName column stores values: Outlet1, Outlet2, Outlet3, etc., you want to display its values as a sOutlet1, sOutlet2 and so on. We need example data, to uderstand what are you trying to achieve and to help you.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-06-30T04:53:00

Please, read my comment to the question.

You're, probably, trying to achieve something like that:

SQL

DECLARE @Branch TABLE(ID INT IDENTITY(1,1), BranchName VARCHAR(30))

INSERT INTO @Branch (BranchName)
SELECT 'Outlet1' AS BranchName
UNION ALL SELECT 'Outlet2' AS BranchName
UNION ALL SELECT 'Outlet3' AS BranchName
UNION ALL SELECT 'Outlet4' AS BranchName
UNION ALL SELECT 'Outlet5' AS BranchName


SELECT 's' + BranchName AS NewOutletName
FROM @Branch

Result:

VB

NewOutletName
sOutlet1
sOutlet2
sOutlet3
sOutlet4
sOutlet5

SQL able to join row data to column?

1 solution

Solution 1

Add your solution here

Preview 0

SQL able to join row data to column?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us