SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Use value in one table as Column name in SELECT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dabooj
Starting Member

United Kingdom
7 Posts

Posted - 03/06/2009 :  07:45:38  Show Profile  Reply with Quote
Hi,

I have a price list which has various price columns for each investigation. The idea is that you have a standard price column & then numerous exceptions columns with values where needed.
To map the correct price to the investigation, i also have a price column mappings table. this looks at the customer & then assigns the column to use for the pricing (either standard price column or one of the exceptions ones).
This mappings table stores the name of the price column to use as one of its values.

What i want to do is to use the price column value in the mappings table as the column name in a select query which pulls the investigation data & the price to charge value together.

I can't use a CASE statement to create the various price column options as these will be dynamic as the pricing columns will increase/decrease in time so i need a dynamic method of using the assigned column name in the mappings table to extract data from the priceing table.

Can anyone help?

Regards,


Shuja Ahmad
Systems Developer

sakets_2000
Flowing Fount of Yak Knowledge

India
1472 Posts

Posted - 03/06/2009 :  07:56:05  Show Profile  Reply with Quote
try posting sample data, output etc.
Go to Top of Page

Bodestone
Starting Member

18 Posts

Posted - 03/06/2009 :  08:00:20  Show Profile  Reply with Quote
The only way to do that would be to use dynamically generated SQL and this has many drawbacks.
If the number of exceptions is going to change over time then maybe adding columns to the table is not the way but rather have a prices table as follows:

InvestigationID INT
PriceType NVARCHAR(50)
Price MONEY

THen you could have data that looks like

InvestigationID PriceType Price
1 Standard 10.99
1 Using Monkeys 16.99


You would then be mapping value to value rather than value to column name.
Go to Top of Page

sakets_2000
Flowing Fount of Yak Knowledge

India
1472 Posts

Posted - 03/06/2009 :  08:02:47  Show Profile  Reply with Quote
If the number of distinct columns is limited to a small number, Your query will look like,,


select * 
from [price column mappings ] join investigation on [standard price column]=...
where
[assigned column]='standard price column'

union all


select * 
from [price column mappings ] join investigation on [exception col 1]=...
where
[assigned column]='exception col 1'

union all

select * 
from [price column mappings ] join investigation on [exception col 2]=...
where
[assigned column]='exception col 2'

union all

...
Go to Top of Page

dabooj
Starting Member

United Kingdom
7 Posts

Posted - 03/06/2009 :  09:34:32  Show Profile  Reply with Quote
Here is a set of sample data:

investigation data table: id, cust_code, cust_name, invest_code, invest_name (1, 5xxx, Dr jones hospital, 1X00, Soft Tissue Cytology ; 2, 6zzz, smith's clinic, 1X00, Soft Tissue Cytology).

PriceList Table: id, investigation_code, standard_price, Smiths clinic price, joe bloggs hospital price ( 1, 1x00, £10, £5, £7 ; 2, 1zp99, £12, £15, £5)


PriceColumnMap: id, cust_code, pricing_column_to_use (1, 5xxx, standard_price; 2, 6zzz, Smiths clinic price)

View to map column to use to investigation data (this is created using case statements to select the column name to use for the customer. Basically, this is the investigation data with the price column name added as a new value at the end column):
id, cust_code, cust_name, invest_code, invest_name, price_column_to_use (1, 5xxx, Dr jones hospital, 1X00, Soft Tissue Cytology, standard_price ; 2, 6zzz, smith's clinic, 1X00, Soft Tissue Cytology, Smiths clinic price).

Now what I need to do is use the price_column_to_use column value & extract the related value from the pricelist table:
id, cust_code, cust_name, invest_code, invest_name, price_column_to_use , Price (1, 5xxx, Dr jones hospital, 1X00, Soft Tissue Cytology, standard_price, £10 ; 2, 6zzz, smith's clinic, 1X00, Soft Tissue Cytology, Smiths clinic price, £5).

This last point is where I’m stuck. Rearranging the existing pricelist is not an option cos the department needs the data to be laid out as it is.

Any ideas of how to achieve this?


Shuja Ahmad
Systems Developer
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000