SQL Server Generate Row from two range value -

i have 1 table call chqbook_tbl :

cbid   cbbankid      cbstartno cbbookendno 1           1           1         5 2           1           21        25 3           2           1        5 

and have table chq_tbl following

c_cbbankid     c_no         1            2          1           22     2            1     2            5 

now need select records data following:

c_bankid  c_no       c_type   1         1        dynamic   1         2        static   1         3        dynamic   1         4        dynamic   1         5        dynamic   1         21       dynamic   1         22       static   1         23       dynamic   1         24       dynamic   1         25       dynamic   2         1        static   2         2        dynamic   2         3        dynamic   2         4        dynamic   2         5        static 

you can use numbers table (like master..spt_values) number in ranges specified cbstartno , cbbookendno , left join chq_tbl , use case expression set dynamic/static attribute depending on null values.

this should work:

select      c1.cbbankid,      m.number c_no,      case when c2.c_no null 'dynamic' else 'static' end c_type master..spt_values m join chqbook_tbl c1 on m.number between c1.cbstartno , c1.cbbookendno left join chq_tbl c2 on m.number = c2.c_no , c1.cbbankid = c2.c_cbbankid m.type = 'p' 

note numbers table used holds values 0-2047 if need higher numbers should either construct suitable table or use recursive query generate numbers on fly.

sample sql fiddle