How to display data of multiple row into one string

Question

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

Please provide your tables structure – Stack Overflow Dec 12 '18 at 07:23 — Stack Overflow, Dec 12 '18 at 07:23

Andrey Nikolov · Answer 1 · 2018-12-12T07:33:03.717

2

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

edited Dec 12 '18 at 07:33

answered Dec 12 '18 at 07:24

Andrey Nikolov

12,967
3
20
32

I'm using SQL Server 2014. The function is not available. – NAJAA BAZILAH Dec 12 '18 at 07:28
I edit the code a little bit to show the results for specific id, i get what I want but the problem is the first data is not showing the full text, it left out the first alphabet of the data. – NAJAA BAZILAH Dec 12 '18 at 07:45

score 1 · Answer 2 · answered Dec 12 '18 at 07:59

    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

score 0 · Accepted Answer · answered Dec 12 '18 at 07:38

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

How to display data of multiple row into one string

3 Answers3