Using "dynamic" column names - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Using "dynamic" column names

Author

Topic

Barneyb
Starting Member

1 Post

Posted - 2006-02-27 : 21:20:05

I'm not sure if I have used the right wording for my subject but I'm not sure what to call it...

Basically I have an order table full of products for customers. I have a products table where there are 30 different combinations of prices for each product (UnitPrice1 - UnitPrice30). In the customer table each customer have a "cost structure" which is a number between 1-30 and is used to pick which UnitPrice column is used for that particular customer.

What I want to do is get the order table with the price of the product next to it based on the price for that particular customer.

The basic query I have so far is:

SELECT Order.BoxNumber,
Order.ProductID,
Order.CustomerID,
Customers.CostStructure,
Products.UnitPrice{0} as UnitPrice
FROM Order
INNER JOIN Customers
ON Order.CustomerID = Customers.CustomerID
AND Customers.CompanyID='1'
INNER JOIN Products
ON Order.ProductID = Products.ProductID

Now this gets everything right except the UnitPrice column. I need to be able to replace the "{0}" with the value from Customers.CostStructure. I hope this makes sense.

Is it possible to do this?
Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-27 : 21:44:40

quote:
I need to be able to replace the "{0}" with the value from Customers.CostStructure

Yes. But with Dynamic SQL like exec() or sp_executesql. But there are other option available.

quote:
I have a products table where there are 30 different combinations of prices for each product (UnitPrice1 - UnitPrice30)

[Option 1]
First of all, if you are able to normalize your table, everything else will be very easy.
You should change it to somehting like

create table product
(
    product        int,
    cost_structure int,
    unitprice      decimal(12,2)
)

[Option 2]
Or alternatively, you can create a view out of your unnormalize table

create view product_price
as
begin
    select product, 1 as coststructure, unitprice1 as unitprice from product union all
    select product, 2 as coststructure, unitprice2 as unitprice from product union all
    select product, 3 as coststructure, unitprice3 as unitprice from product union all
    . . .
end

[Option 3]
Create a function to return the unit price

create function product_price
(
    @product       int,
    @coststructure int
)
returns decimal(12,2)
as
begin
    declare @unitprice decimal(12,2)
    select  @unitprice =
            case @coststructure
            when  1 UnitPrice1
            when  2 UnitPrice2
            . . .
            when 30 UnitPrice30
            end
    from    product
    where   product = @product
end

----------------------------------
'KH'

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-28 : 08:31:39

Option #1 is the way to go. Always normalize your data, not only are SELECT's like this easier to write, but your data is easier to maintain.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources