I have a table named Skill which store the skills data. the skill level '2337' is the Advanced , '2338' is the Intermediate, '2339' is the beginner . I want to display the skills name data based on the skill level that looks like this For example, Technical Profile_ID = 33
Advanced = Javascript, SQL, C#
Intermediate = Php
Beginner = vb,Java
I'm new to VB, can anyone help me to solve this.I'm using VB.net and database Microsoft SQL
You can use STRING_AGG aggregation function on SQL Server 2017 or newer:
select SkillLevel, STRING_AGG(SkillName, ', ') as SkillNames
from Skills
group by SkillLevel
If you are on older version, see how to use FOR XML clause in this question: Building a comma separated list?
, but the code should look like this:
select SkillLevel, (SELECT stuff((
SELECT ', ' + cast(SkillName as varchar(max))
FROM Skills s2
WHERE s2.SkillLevel = s1.SkillLevel
FOR XML PATH('')
), 1, 2, ''))
from Skills s1
group by SkillLevel
SELECT SkillLevel,
SkillName = STUFF((SELECT ', ' + SkillName from tblSkill b where TechnicalProfile_ID = '33' AND b.SkillLevel = a.SkillLevel
For XML PATH('')),1,2, '')
FROM tblSkill a
Group by SkillLevel
The output is like
SkillLevel SkillName
2337 Javascript,SQL,C#
2338 Php
2339 Vb,Java
You can try this
create table #temp (skillid int, technicalprofile_id int, skilllevel int, skillname varchar(50))
insert into #temp values
(35, 21, 2339, 'Php'),
(36, 21, 2339, 'Laravel')
SELECT skilllevel, skillname =
STUFF((SELECT ', ' + skillname
FROM #temp b
WHERE b.skilllevel = a.skilllevel
FOR XML PATH('')), 1, 2, '')
FROM #temp a where skilllevel = 2339
GROUP BY skilllevel
The output is as shown below
skilllevel skillname
2339 Php, Laravel