use COALESCE function for platform and skills columns to get result platform followed by skills with comma

Question

1.00/5 (4 votes)

See more:

SQL-Server-2005

SQL

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

Posted 24-Sep-13 1:12am

Member 10256268

Updated 24-Sep-13 1:25am

v3

Add a Solution

Comments

Member 10256268 24-Sep-13 7:18am

the result should be like this
platform skills
.net ASP.NET 2010,c# 3.0
software tes qa, manual

Maciej Los 24-Sep-13 7:21am

Improve question instead using comment.

Maciej Los 24-Sep-13 7:24am

Where is a problem?

Member 10256268 24-Sep-13 7:26am

no problem in this code ,i should get platform followed by skills with comma

Member 10256268 24-Sep-13 7:27am

u see above result ,i should get result like that

Member 10256268 24-Sep-13 7:27am

by using COALESCE function

Member 10256268 24-Sep-13 7:49am

expted output should be like this 1st row DOT NET=ASP.NET 2010, c# 3.0, WCF, c#4.0
2nd row Database=Microsoft SQL Server 2005

Maciej Los 24-Sep-13 7:47am

Use "Reply" button to reply to others comment.
Please, provide more details (sample data and expected output).

Member 10256268 24-Sep-13 7:56am

expted output should be like this 1st row DOT NET=ASP.NET 2010, c# 3.0, WCF, c#4.0
2nd row Database=Microsoft SQL Server 2005

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kirill Borunov · Accepted Answer · 2013-09-24T03:06:00

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