auto colomn names from data

[alexwhit]

i wonder if you can help. this may be in the wrong forum.

i want to produce an auto alias for a group of price bands. the names of what i want the aliases to be are stored in a table and these can change. is there a way to automatically create these and keep them updated?

thanks

 

[r937]

could you give (1) a few rows of data from the table that has the alias names, and (2) the query statement that you want to have the "auto" aliases

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[alexwhit]

SELECT
"StockItem"."Code",
"PriceBand"."Name",
"StockItem"."Manufacturer",
"StockItem"."StockItemStatusID",
"StockItem"."StandardCost",
"StockItemPrice"."Price",

(SELECT "StockItemPrice"."Price" WHERE ("PriceBand"."Name"= 'standard')) AS "PriceBand Standard"

FROM ("WhatMoreLive"."dbo"."StockItemPrice" "StockItemPrice" INNER JOIN "WhatMoreLive"."dbo"."StockItem" "StockItem"

ON "StockItemPrice"."ItemID"="StockItem"."ItemID") INNER JOIN "WhatMoreLive"."dbo"."PriceBand" "PriceBand"
ON "StockItemPrice"."PriceBandID"="PriceBand"."PriceBandID"
WHERE "StockItem"."StockItemStatusID"=0

 

[alexwhit]

the table i want just has

id name
1 Standard
2 price band 1
3 price band 2

ect

 

[r937]

where exactly are the price bands defined?

i assumed that a "price band" was something like

10.00 - 14.99 = cheap
15.00 - 29.99 = normal
30.00 - 49.99 = expensive


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[alexwhit]

yea something like that. i can create them manuly but the amount of bands could change

 

[r937]

okay, you have a subquery in the SELECT clause like this --

SELECT "StockItem"."Code"
, ...
, ( SELECT "StockItemPrice"."Price"
WHERE "PriceBand"."Name" = 'standard'
) AS "PriceBand Standard"
FROM ...

but this obviolsy doesn't work (because the subquery is missing the FROM clause)

how did you figure that you were supposed to use "standard" as the price band and not one of the other ones?

i've seen queries like this before, and what usually happens is that the actual price of an item determines which band it's in...

SELECT ...
, pricebands.bandname -- e.g. standard
FROM items
INNER
JOIN pricebands
ON items.price BETWEEN pricebands.loprice and pricebands.hiprice

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[alexwhit]

thanks for the reply.

the sub querey creates the comlomn name standard. nd fills in the info. what i am alooking for is somethinkg like that but creates a colomn for every "price band name" record in the priceband table.

thanks

 

[alexwhit]

the prices are not stored in this table they are stored elsewhere.

 

[alexwhit]

could a pivot be used??

 

[alexwhit]

i ended up creating a pivot table and then adding it as a stored procedure.

now working like a dream