问使用COALESCE串联值

EN

SELECT 
    Empcode, Name, 
    GROUP_CONCAT(Skill) 
FROM <table_name> 
GROUP BY Empcode 
ORDER BY Primary DESC

SELECT  Empcode, Name, Skills
FROM   yourTable a
CROSS APPLY --or OUTER APPLY
(
    SELECT SUBSTRING(
        (SELECT ','+b.Skill FROM yourTable b
         WHERE   a.Empcode = b.Empcode FOR XML PATH(''))
    ,2 ,4000) Skills
) x

-- First order all the secondary skills with a row number
with rows as (
    select 
        empcode, skill, row_number() over (partition by empcode order by skill) as row
    from dbo.table_1
    where [primary] is null and skill is not null
),
-- Second use recursive CTE to create concat the values
cte(empcode,skill,row) as (

    select empcode, cast(skill as varchar(MAX)), row
    from rows where row=1
    union all
    select r.empcode, cast(concat(c.skill, ',', r.skill) as varchar(MAX)), r.row
    from
    cte c inner join rows r on r.row = c.row + 1
),
-- Third sort the resulting CTE so that can filter to only the end of 
--the recursive chain
ordered(empcode,skill,row) as (
    select empcode,skill, row_number() over (Partition by empcode order by row desc)
    from cte
)

-- Now fetch the results
select
    t.empcode,t.name, t.skill as [primary skill], o.skill as [secondary skill]
from
table_1 t
left outer join ordered o on t.empcode=o.empcode and o.row=1
where t.[Primary] = 'Yes'