表1
history_id card_code money_input operate_date
1 1 100 2005-10-30 9:00:00
2 1 300 2005-10-31 12:00:00表2
history_id card_code money_add operate_date
1 1 200 2005-10-30 12:30:00表3
history_id card_code consume_name consume_date_end
1 1 M6 2005-10-31 14:30:00
2 1 美白祛班疗程 2005-10-31 16:00:00取2005-10-31 数据
产生临时表如下
card_code money_input operate_date consume_name1 consume_date_end1 consume_name2 coume_date_end2
1 300 2005-10-31 12:00:00 M6 2005-10-31 14:30:00 美白祛班疗程 2005-10-31 16:00:00
history_id card_code money_input operate_date
1 1 100 2005-10-30 9:00:00
2 1 300 2005-10-31 12:00:00表2
history_id card_code money_add operate_date
1 1 200 2005-10-30 12:30:00表3
history_id card_code consume_name consume_date_end
1 1 M6 2005-10-31 14:30:00
2 1 美白祛班疗程 2005-10-31 16:00:00取2005-10-31 数据
产生临时表如下
card_code money_input operate_date consume_name1 consume_date_end1 consume_name2 coume_date_end2
1 300 2005-10-31 12:00:00 M6 2005-10-31 14:30:00 美白祛班疗程 2005-10-31 16:00:00
[create] table t1
(history_id int,card_code int,money_input int,operate_date datetime)insert t1
select 1,1,100,'2005-10-30 9:00:00' [union] all
select 2,1,300,'2005-10-31 12:00:00' create table t2
(history_id int,card_code int,money_add int,operation_date datetime)insert t2 values (1,1,200,'2005-10-30 12:30:00')create table t3
(history_id int,card_code int,consume_name varchar(30),consume_date_end datetime)
insert t3
select 1,1,'M6','2005-10-31 14:30:00' [union] all
select 2,1,'美白祛班疗程','2005-10-31 16:00:00'declare @sql varchar(5000),@i int,@d datetime
select @sql='',@i=0
select @d='2005-10-31'select @i=@i+1,@sql=@sql+case when b.money_add is null then ''
else ',[money_add'+cast(@i as varchar)+']=max(case when a.card_code=
'''+cast(a.card_code as varchar)+''' then b.money_add end)'
end,@sql=@sql+case when b.operation_date is null then ''
else ',[operation_date'+cast(@i as varchar)+']=max(case when a.card_code=
'''+cast(a.card_code as varchar)+''' then b.operation_date end)' end,
@sql=@sql+case when c.consume_name is null then ''
else ',[consume_name'+cast(@i as varchar)+']=max(case when c.consume_name=
'''+c.consume_name+''' then c.consume_name end)' end,
@sql=@sql+case when c.consume_date_end is null then ''
else ',[consume_date_end'+cast(@i as varchar)+']=max(case when c.consume_date_end=
'''+cast(c.consume_date_end as varchar)+''' then c.consume_date_end end)' end
from t1 a left join t2 b on convert(char(10),a.Operate_date,120)=
convert(char(10),b.operation_date,120) full join t3 c on convert(char(10),a.Operate_date,120)
=convert(char(10),c.consume_date_end,120)
where convert(char(10),operate_date,120)=convert(char(10),@d,120)select @sql='select min(a.card_code) as card_code,max(a.money_input) as money_input,
max(a.operate_date) as operate_date'+@sql+'
from t1 a left join t2 b on convert(char(10),a.Operate_date,120)=
convert(char(10),b.operation_date,120) full join t3 c on convert(char(10),a.Operate_date,120)
=convert(char(10),c.consume_date_end,120)
where convert(char(10),operate_date,120)=convert(char(10),'''+convert(char(10),@d,120)+''',120)
group by a.card_code,a.history_id'exec(@sql)
drop table t1
drop table t2
drop table t3/*-----------------2005-10-31日结果------------------*/
card_code money_input operate_date consume_name1 consume_date_end1 consume_name2 consume_date_end2
----------- ----------- ------------------------------------------------------ ------------------------------ ------------------------------------------------------ ------------------------------ ------------------------------------------------------
1 300 2005-10-31 12:00:00.000 M6 2005-10-31 14:30:00.000 美白祛班疗程 2005-10-31 16:00:00.000警告: 聚合或其它 SET 操作消除了空值。/*--------------2005-10-30日结果-----------------*/
card_code money_input operate_date money_add1 operation_date1
----------- ----------- ------------------------------------------------------ ----------- ------------------------------------------------------
1 100 2005-10-30 09:00:00.000 200 2005-10-30 12:30:00.000
你可能会错我的意思了
我要的是那在当天三个表有一条数据,则临时表加一次两个字段 money date
两条就变成money1 date1 money2 date2
当天没有记录就不用加了
关于行变列看这个:
http://community.csdn.net/Expert/topic/4324/4324734.xml?temp=.5537836
我那三个表都可能变换
那个链接在看