auto colomn names from data

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

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

the table i want just has

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

ect

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

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

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

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

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

could a pivot be used??

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

now working like a dream