解决方案 »
- 多表数据查询
- 数据库从sql2000换到sql2005后,访问网站的登录页面时显示是空白,得刷新一下才能出来登录界面
- 急,导出的数据是乱码!
- 如何将一个unicode字符串(如:"%u5C0F")转换成汉字,%u5C0F是一个字符串!
- 如何用最简单的办法将sqlserver中的某个表的表结构连同表里面的数据保存起来存储到其他数据库中?
- c/s应用中,客户端不定期出现非常慢的现象。
- 有奖答题了:数据库方面的(3)
- 如何在存储过程调用SQL的命令,并且得到返回结果
- 有知道如何估算表存储空间的兄弟吗?
- 如何将数据库表中某列的值在存储过程里自动执行
- 本机sqlserver2008数据库如何备份到虚拟机中的数据库
- 关于多条件触发器的问题
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
(
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
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)
我转动态一下