×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

auto colomn names from data

auto colomn names from data

auto colomn names from data

(OP)
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
 

RE: 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

RE: auto colomn names from data

(OP)
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
 

RE: auto colomn names from data

(OP)
the table i want just has

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

ect

RE: auto colomn names from data

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

RE: auto colomn names from data

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

RE: auto colomn names from data

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

RE: auto colomn names from data

(OP)
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

RE: auto colomn names from data

(OP)
the prices are not stored in this table they are stored elsewhere.  

RE: auto colomn names from data

(OP)
could a pivot be used??

RE: auto colomn names from data

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

now working like a dream

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close