CODEWITHSUNDEEP
sqlsql-servervb.net
NAJAA BAZILAH
NAJAA BAZILAH

Reputation: 87

How to display data of multiple row into one string

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

enter image description here

Upvotes: 0

Views: 220

Answers (3)

NAJAA BAZILAH
NAJAA BAZILAH

Reputation: 87

    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

Upvotes: 1

Suraj Kumar
Suraj Kumar

Reputation: 5643

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

Upvotes: 0

Andrey Nikolov
Andrey Nikolov

Reputation: 13450

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

Upvotes: 2

Related Questions