sql server - SQL, Firebird, Dynamic Union based on other Table -

i need help. need build dynamic union sql statement. (or stored procedure)

in tablea1 there values stored sql parameters. example:

id | name 1 | 16 2 | 23 3 | 30 

in tableb1 there other values stored. need every row in tablea1 union

for example:

select b.* tableb1 b join tablea1 on a.id = b.id b.id = 1 union select b.* tableb1 join tablea1 on a.id = b.id b.id = 2 

because tablea1 dynamic, not possible make static union-sql. please me. maybe with: cte ?

best regards rf

second explanation: try make explanation more understandable. sorry first question. see not correct.

i need dynamic union (or else) because of table design of tableb1. values stored each month/years in horizontal way.

example tableb1:

 date    | xxxx | id1 |  id2 | id3 | id4 ... 2014-07 | n    |  20 |   30 |  40 | 2014-08 | n    |  40 |   50 |  70 | 

the output should this:

 tableb1.date | tablea1.id | tablea1.name | tableb1.id x | 2014-07      |          1 |         16   |    20     2014-08      |          1 |         16   |    40 2014-07      |          2 |         23   |    30       2014-08      |          2 |         23   |    50 2014-07      |          3 |         30   |    40       2014-08      |          3 |         30   |    70 

i thought put sql stored procedure, because each union have change sql syntax right column.

maybe have make unpivot stored procedure first. have idea how make in firebird?

i not sure field xxxx in tableb1 represents assuming not have id field in tableb1, , each id in tablea1 want corresponding idx field tableb1. assuming id fields numeric , name numeric. here example code inside execute block, can put stored procedure. reason execute block is makes flexible if being executed via ado example.

i expanded on idea frazz said restructure tableb1.

you need following temp table

create global temporary table gtt_temp (     dt   varchar(10),     id   integer,     val  integer ) on commit delete rows; 

and here sql

execute block returns (tableb1_date varchar(10),          tablea1_id integer,          tablea1_name integer,          tableb1_id integer ) declare variable sql varchar(1000); declare variable id integer; begin          select tablea1.id tablea1     :id         begin        sql = 'insert gtt_temp ( dt, id, val ) select tableb1."date", '||:id||', id'||:id||' tableb1 tableb1.id'||:id||' > 0' ;        execute statement :sql;     end           select gtt_temp.dt, gtt_temp.id, tablea1.name, gtt_temp.val gtt_temp, tablea1 tablea1.id = gtt_temp.id     :tableb1_date,          :tablea1_id,          :tablea1_name,          :tableb1_id           begin         suspend;      end  end