Query to order data from one table (rows) and another table (colums)

[Copernicus98]

I need a query to get data from one table and order it by data from the columns of another table.

Heres what I have.

Table SegBc has the following data in two columns (BC and Demand are column names)

BC Demand
A 25
B 32
D 12
F 19
Z 30

Table BCOrder has just one row and the following columns (BC01, BC02, etc. are column names).

BC01 BC02 BC03 BC04 BC05 BC06
D Z B A F

I would like to get the records from SegBc and order it by the columns from BCorder.. So this is what I want

BC Demand
D 12
Z 30
B 32
A 25
F 19

Would appreciate a query to get this in one shot. Thanks very much.

 

[SemperFiDownUnda]

what you want to do is a INNER JOIN. You can order by the right table columns but only display the left tables columns you are interested in.

check out JOIN Fundamentals faq183-4785

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[gtr111]

Copernicus98,
Maybe you can just normalize the BCOrder table to
BcId (numeric to sort the values)
BcVal (the char value)

join SegBc to BCOrder and order by BcId

If you can't play with BCOrder (maybe because BCOrder is used, as is, in too many other places) perhaps you can just create a normalized temp table built from BCOrder.

 

[Andel]

here's one solution. it works but ugly.

select s.* from segbc s
join (select name, val = case
when name = 'bc01' then (select bc01 from bcorder)
when name = 'bc02' then (select bc02 from bcorder)
when name = 'bc03' then (select bc03 from bcorder)
when name = 'bc04' then (select bc04 from bcorder)
when name = 'bc05' then (select bc05 from bcorder)
when name = 'bc06' then (select bc06 from bcorder)
end
from syscolumns where id = object_id('BCOrder')) as x
on s.bc = x.val


Andel
andel@barroga.net

http://andyb.8m.com

 

[Copernicus98]

Thanks everyone for your valuable insights. I am going to have to build a temp table to do what I want. Is there a SQL Query that tells me if a table exists?