使用COALESCE函数获取平台和技能列，以获得结果平台，然后使用逗号技能 [英] use COALESCE function for platform and skills columns to get result platform followed by skills with comma

问题描述

 选择 TM_UserAttributeValue.TM_UserID，TM_User.FullName，Reference.LookupID，Reference_Skills.SkillLookupKey，Reference .LookupValue  as  Platfrom，
 Reference_Skills.SkillLookupValue  as  skills 
 来自参考
  join  Reference_Skills  on  Reference.LookupID = Reference_Skills.SkillLookupKey 
  join  TM_UserAttributeValue  on  Reference_Skills.SkillLookupValue = TM_UserAttributeValue.AttributeValue 
  join  TM_User   TM_UserAttributeValue.TM_UserID = TM_User.TM_UserID 
  where  AttributeKeyType = ' 技能' 和 TM_UserAttributeValue.TM_UserID = 106 

结果应该是这样的

平台技能
 .net ASP.NET 2010，c＃3.0 
软件测试qa，手册

解决方案

好的，完整的解决方案：

选择u.TM_UserID，
 u.FullName，
 r.LookupValue as Platfrom，
 STUFF（（
 SELECT'，'+ xrs.SkillLookupValue 
 FROM Reference_Skills xrs 
加入TM_UserAttributeValue xua on xua.AttributeValue = xrs.SkillLookupValue 
 WHERE xrs.SkillLookupKey = r.LookupID 
和xua.AttributeKeyType = ua.AttributeKeyType 
和xua.TM_UserID = u.TM_UserID 
 FOR XML PATH（''）
） ，1,2，''）技能
来自TM_User u 
加入TM_UserAttributeValue ua on ua.TM_UserID = u.TM_UserID和ua.AttributeKeyType ='Skill'
加入Reference_Skills rs on rs。 SkillLookupValue = ua.AttributeValue 
 join参考r r.LookupID = rs.SkillLookupKey 
其中u.TM_UserID = 106 
 group by r.LookupID，r.LookupValue，u.TM_UserID，u.FullName，ua.AttributeKeyType 

select TM_UserAttributeValue.TM_UserID,TM_User.FullName,Reference.LookupID,Reference_Skills.SkillLookupKey,Reference.LookupValue as Platfrom,
 Reference_Skills.SkillLookupValue as skills
  from Reference
 join Reference_Skills on Reference.LookupID=Reference_Skills.SkillLookupKey
 join TM_UserAttributeValue on  Reference_Skills.SkillLookupValue=TM_UserAttributeValue.AttributeValue
 join TM_User on TM_UserAttributeValue.TM_UserID=TM_User.TM_UserID 
 where AttributeKeyType ='Skill' and TM_UserAttributeValue.TM_UserID=106

the result should be like this

platform skills
.net ASP.NET 2010,c# 3.0
software tes qa, manual

解决方案

Ok, the full solution:

select	u.TM_UserID,
	u.FullName,
	r.LookupValue as Platfrom,
	STUFF((
		SELECT	', ' + xrs.SkillLookupValue
		FROM	Reference_Skills xrs
			join TM_UserAttributeValue xua on xua.AttributeValue = xrs.SkillLookupValue
		WHERE	xrs.SkillLookupKey = r.LookupID
			and xua.AttributeKeyType = ua.AttributeKeyType
			and xua.TM_UserID = u.TM_UserID
		FOR XML PATH('')
	) ,1,2,'') as skills
from	TM_User u
	join TM_UserAttributeValue ua on ua.TM_UserID = u.TM_UserID and ua.AttributeKeyType = 'Skill'
	join Reference_Skills rs on rs.SkillLookupValue = ua.AttributeValue
	join Reference r on r.LookupID = rs.SkillLookupKey
where	u.TM_UserID = 106
group by r.LookupID, r.LookupValue, u.TM_UserID, u.FullName, ua.AttributeKeyType

这篇关于使用COALESCE函数获取平台和技能列，以获得结果平台，然后使用逗号技能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！