如果有这么两张表,
T1:
-----------------
T1_C1
1
2T2:
---------------------------
T2_C1 T2_C2 T2_C3
1 A C3_1
1 B C3_2
1 C C3_3
1 D C3_4
2 B C3_6想要得到T3这个么结果,
T3
------------------------------------------------
T3_C1 T3_C2 T3_C3 T3_C4 T3_C5
1 C3_1 C3_1 C3_1 C3_1
2 NULL C3_6 NULL NULLL说明:即要以T1表的T1_C1 去关联 T2表的T2_C1(T1_C1=T2_C1),然后在得到的结果里,判断,如果T2_C2的内容为'A'就将T2_C3的内容填到 T3_C2 ,如果为'B'就将内容填到T3_C3,依此类推... 有哪位兄弟做过这样子的处理么?您当时是怎么实现的呢? 或者给一个思路也好,谢谢~~~
T1:
-----------------
T1_C1
1
2T2:
---------------------------
T2_C1 T2_C2 T2_C3
1 A C3_1
1 B C3_2
1 C C3_3
1 D C3_4
2 B C3_6想要得到T3这个么结果,
T3
------------------------------------------------
T3_C1 T3_C2 T3_C3 T3_C4 T3_C5
1 C3_1 C3_1 C3_1 C3_1
2 NULL C3_6 NULL NULLL说明:即要以T1表的T1_C1 去关联 T2表的T2_C1(T1_C1=T2_C1),然后在得到的结果里,判断,如果T2_C2的内容为'A'就将T2_C3的内容填到 T3_C2 ,如果为'B'就将内容填到T3_C3,依此类推... 有哪位兄弟做过这样子的处理么?您当时是怎么实现的呢? 或者给一个思路也好,谢谢~~~
INSERT INTO 表A SELECT 1,'a'
UNION ALL SELECT 2,'b'
UNION ALL SELECT 3,'c'
UNION ALL SELECT 4,'d'
UNION ALL SELECT 5,'e'
UNION ALL SELECT 6,'f'
UNION ALL SELECT 7,'g'CREATE TABLE 表B(体检编号 Varchar(20),项目 Varchar(10),结果 Varchar(10))
INSERT INTO 表B SELECT '200607220001','a','正常'
UNION ALL SELECT '200607220001', 'b','正常'
UNION ALL SELECT '200607220001', 'c','不正常'
UNION ALL SELECT '200607220002', 'a','不正常'
UNION ALL SELECT '200607220002', 'b','正常'
UNION ALL SELECT '200607220002', 'f','正常'
UNION ALL SELECT '200607220002', 'g','正常'select * from 表A
select * from 表BDeclare @sql varchar(8000)
set @sql='select 体检编号'
select @sql=@sql+',max(case 项目 when'''+项目+''' then 结果 else NULL end)['+项目+']'
from (select distinct 项目 from 表A) as a
select @sql=@sql+'from 表B group by 体检编号'
print @sql
exec(@sql)select 体检编号,max(case 项目 when'a' then 结果 else NULL end)[a],max(case 项目 when'b' then 结果 else NULL end)[b],max(case 项目 when'c' then 结果 else NULL end)[c],max(case 项目 when'd' then 结果 else NULL end)[d],max(case 项目 when'e' then 结果 else NULL end)[e],max(case 项目 when'f' then 结果 else NULL end)[f],max(case 项目 when'g' then 结果 else NULL end)[g]from 表B group by 体检编号参考这个两表间的行转列
insert T1
select 1 union select 2create table T2(T2_C1 int,T2_C2 varchar(10),T2_C3 varchar(10))
insert T2
select 1, 'A', 'C3_1' union
select 1, 'B', 'C3_2' union
select 1, 'C', 'C3_3' union
select 1, 'D', 'C3_4' union
select 2, 'B', 'C3_6'select T2.*,ID=identity(int,1,1) into #T from T2 left join T1 on T2.T2_C1=T1.T1_C1declare @s varchar(1000)
set @s=''
select @s=@s+',['+T2_C2+']=max(case T2_C2 when '''+T2_C2+''' then T2_C3 end)'
from (select distinct T2_C2 from #t)A order by T2_C2
select @s='select '+stuff(@s,1,1,'')+' from (select *,(select count(1) from #T
where T2_C2=a.T2_C2 and ID<=a.ID) Count from #t a)b group by count'exec(@s)drop table T1,T2,#T
----------------------------------------
A B C D
---------- ---------- ---------- ----------
C3_1 C3_2 C3_3 C3_4
NULL C3_6 NULL NULL
就差表头了,自己改改?