做做

[yubofighting]

[xuam]

看看.

[--小F--]

left join 3次？

[-晴天]

想想

select a.EmpID,ProjID,a.ApplicationGrp,b.Skill,b.Proficiency from(
select *,rn=row_number()over(order by (select 1)) from t1)a inner join (
select *,rn=row_number()over(order by (select 1)) from t1)b on a.rn=b.rn

[NBDBA]

本质是行列转换，一个更乱的行列转换

[chuanzhang5687]

create table t2
(
	empid	int,
	skill	varchar(10),
	proficiency	varchar(10)
)
insert into t2 values(154302,'SSAS','Novice')
insert into t2 values(154302,'SSIS','Expert')
insert into t2 values(154302,'SSRS','Learner')
create table t1
(
	empid	int,
	proid	int,
	applicationGroup	varchar(10)
)
insert into t1 values(154302,111111,'Retail')
insert into t1 values(154302,123456,'Pharmacy')

select empid,proid,applicationGroup,
	skill1 = 'SSAS',
	max(case when skill = 'SSAS' then proficiency end) as proficiency,
	skill2 = 'SSIS',
	max(case when skill = 'SSIS' then proficiency end) as proficiency,
	skill3 = 'SSRS',
	max(case when skill = 'SSRS' then proficiency end) as proficiency
from 
(
	select a.* ,b.skill,proficiency from t1 a left join t2 b on a.empid = b.empid
) tb
group by empid,proid,applicationGroup
/*
empid,proid,applicationGroup,skill1,proficiency,skill2,proficiency,skill3,proficiency
154302,111111,Retail,SSAS,Novice,SSIS,Expert,SSRS,Learner
154302,123456,Pharmacy,SSAS,Novice,SSIS,Expert,SSRS,Learner
警告: 聚合或其他 SET 操作消除了空值。

(2 行受影响)

[稻草_木偶]

select a.empid,a.proid,a.applicationGroup,b.skill as'skill1',b.proficiency,c.skill as 'skill2',
c.proficiency,d.skill as 'skill3',d.proficiency
from t1 a left join (select *  from t2 where skill='ssas')b 
on b.empid = a.empid
 left join (select * from t2 where skill='ssis')c 
on c.empid = a.empid
 left join (select * from t2 where skill='ssrs')d 
on d.empid = a.empid

[yubofighting]

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when skill='''+skill+''' then skill end) [skill'+skill+'],
max(case when skill='''+skill+''' then proficiency end)[proficiency]' from (select * from B)t
set @sql=STUFF(@sql,1,1,'')
set @sql='select empid,ProjID,ApplicationGrp,'+@sql+' from (select a.* ,b.skill,proficiency from  a left join  b on a.empid = b.empid)t group by empid,ProjID,ApplicationGrp'
print @sql
exec(@sql)

我转动态一下