救命。~~~~~ 很难的一个问题，难倒了好几个QQ群的问题。
有两个数据表
表1
A C D
1 a 1
2 b 2
3 c 2
4 d 32
表2
A F G
2 f 3
3 g 1
4 h 2
现在想实现这样的结构。
A a b c d e f g h
1 1 . . . . . . .
2 . 2 . . . 3 . .
3 . . 2 . . . 1 .
4 . . . 32 . . . 2
". "代表空
救命 要不就被开除了
[解决办法]
不知所云,帮顶
[解决办法]
交叉表啊.
[解决办法]
DECLARE @Sql nvarchar(4000)SET @Sql = 'SELECT A 'SELECT @sql = @sql + ',SUM(case C when ' ' '+ C + ' ' ' then D Else null end) as ' + C FROM (SELECT A,C, D FROM T1 UNION ALL SELECT A,F,G FROM T2) AS AAset @sql = @sql + ' from (Select * FROM t1 union select * FROM t2) AS AA GROUP BY A ' exec(@sql)
[解决办法]
lz把上面的sql写成个存储过程调用就可以了.
[解决办法]
这样实现是可以,但是效率不高,如果数据量大的话会有问题.
[解决办法]
Drop table a
drop table b
Create table a
(
A int,
C nvarchar(10),
D int
)
go
Create table b
(
A int,
F nvarchar(10),
G int
)
go
insert into a select 1, 'a ',1
union all
select 2, 'b ',2
union all
select 3, 'c ',2
union all
select 4, 'd ',32
insert into b select 2, 'f ',3
union all
select 3, 'g ',1
union all
select 4, 'h ',2
select C.A,max(case C when 'a ' then D end)as a ,max(case C when 'b ' then D end) as b,
max(case C when 'c ' then D end) as c,max(case C when 'd ' then D end)as d,
max(case F when 'f ' then G end)as f,max(case F when 'g ' then G end)as g,
max(case F when 'h ' then G end) as h
from (select a.A,C,D,F,G from a left join b on a.A=b.A)as C Group by C.A
A a b c d f g h
11NULLNULLNULLNULLNULLNULL
2NULL2NULLNULL3NULLNULL
3NULLNULL2NULLNULL1NULL
4NULLNULLNULL32NULLNULL2
[解决办法]
create table #t(a varchar(1),b int)
insert #t(
select C,D from 表1
union all
select F,G from 表2)
DECLARE @a varchar(1),@b int，strSQL varchar(2000)
set strSQL= 'create table ##mytaba (A int '
--游标#t动态生成表结构语句
DECLARE a_Cursor CURSOR FOR select a from #t
Open a_Cursor
FETCH NEXT FROM a_Cursor into @a
WHILE @@FETCH_STATUS = 0
BEGIN
set strSQL=strSQL+ ', '+@a
FETCH NEXT FROM a_Cursor into @a
END
CLOSE a_Cursor
DEALLOCATE a_Cursor
set strSQL=strSQL+ ') '
--生成表结构
exec(@strSQL)
select * into #mytaba from ##mytaba
drop table ##mytaba
--插入A字段
insert #mytaba(
select a from (select a from 表1
union all
select a from 表2)aa
group by a
)
--select * from #mytaba
下面的应该会做了吧，再游标一下表1和表2根据A字段的值和mytaba字段的值对应动态生成更新语句就可以了，应该不是很难，祝你成功
