if object_id('[TB1]') is not null drop table [TB1]
go
create table [TB1] (ID int,CODE nvarchar(6),NAME nvarchar(6))
insert into [TB1]
select 1,'001','土豆丝' union all
select 2,'002','炒黄瓜'

if object_id('[TB2]') is not null drop table [TB2]
go
create table [TB2] (ID int,CODE nvarchar(6),Type int,StartDate datetime,EndDate datetime)
insert into [TB2]
select 1,'001',0,'2012-5-11','2012-5-30' union all
select 2,'002',1,'2012-5-10','2012-5-20'

select * from [TB1]
select * from [TB2]


SELECT  A.id ,
        A.code ,
        A.NAME ,
        CASE WHEN B.TYPE = 0 THEN 'True'
             ELSE ''
        END '推荐菜' ,
        CASE WHEN B.TYPE = 0 THEN B.startdate
             ELSE NULL
        END  AS '开始时间0',
        CASE WHEN B.TYPE = 0 THEN B.enddate
             ELSE NULL
        END AS '结束时间0',
        CASE WHEN B.TYPE = 1 THEN 'True'
             ELSE ''
        END '特价菜' ,
        CASE WHEN B.TYPE = 1 THEN B.startdate
             ELSE NULL
        END  AS '开始时间1',
        CASE WHEN B.TYPE = 1 THEN B.enddate
             ELSE NULL
        END AS '结束时间1'
FROM    TB1 A
        INNER JOIN TB2 B ON A.code = b.code
        
/*      
id          code   NAME   推荐菜  开始时间0                   结束时间0                   特价菜  开始时间1                   结束时间1
----------- ------ ------ ---- ----------------------- ----------------------- ---- ----------------------- -----------------------
1           001    土豆丝    True 2012-05-11 00:00:00.000 2012-05-30 00:00:00.000      NULL                    NULL
2           002    炒黄瓜         NULL                    NULL                    True 2012-05-10 00:00:00.000 2012-05-20 00:00:00.000

(2 row(s) affected)*/