Use value in one table as Column name in SELECT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Use value in one table as Column name in SELECT

Author

Topic

dabooj
Starting Member

7 Posts

Posted - 2009-03-06 : 07:45:38

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
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-06 : 07:56:05

try posting sample data, output etc.

Bodestone
Starting Member

18 Posts

Posted - 2009-03-06 : 08:00:20

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.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-06 : 08:02:47

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

...

dabooj
Starting Member

7 Posts

Posted - 2009-03-06 : 09:34:32

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources