SQL Server Concatenate Multiple Rows By Group Using FOR XML PATH

Question

I have a table called findhighest_secondstep with data like this:

id  department  top_employee    highest_salary  rownumber1
-----------------------------------------------------------
 5  Finance         Shane           6300            1
10  Finance         Laura           6300            1
 7  HR              Vik             7200            1
 3  IT              Kate            7500            1
14  Marketing       Elice           6800            1
 6  Sales           Shed            8000            1

I want to return a table with columns department, top_employee and highest_salary while I know in Finance department we have 2 people having the same salary. So I want to show all of them.

SELECT 
    hs.department AS department,
    top_employee = STUFF((SELECT ', ' + top_employee
                          FROM findhighest_secondstep 
                          FOR XML PATH('')), 1, 1, '')                   
FROM 
    findhighest_secondstep hs
GROUP BY 
    hs.department

This is what I got:

department    top_employee
--------------------------------------------------
Finance       Shane, Laura, Vik, Kate, Elice, Shed
HR            Shane, Laura, Vik, Kate, Elice, Shed
IT            Shane, Laura, Vik, Kate, Elice, Shed
Marketing     Shane, Laura, Vik, Kate, Elice, Shed
Sales         Shane, Laura, Vik, Kate, Elice, Shed

What I want:

department  top_employee    highest_salary
---------------------------------------------
Finance     Shane, Laura    6300
HR          Vik             7200
IT          Kate            7500
Marketing   Elice           6800
Sales       Shed            8000

Can you tell us which version of SQL Server you are using? There may be a much easier to get the output you need. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 23, 2019 at 15:40
You need to link the outmost table with the inner select, making the subquery correlated. You can see now that your subquery is selecting all records from findhighest_secondstep, on every row. — EzLo
– EzLo, Commented Sep 23, 2019 at 15:50

Gordon Linoff · Accepted Answer · 2019-09-23 15:41:01Z

7

You need a correlated subquery:

SELECT hs.department AS department,
       STUFF( (SELECT ', ' + top_employee
               FROM findhighest_secondstep hs2
               WHERE hs2.department = hs.department
               FOR XML PATH('')
              ), 1, 2, ''
            ) as top_employees                   
FROM findhighest_secondstep hs
GROUP BY hs.department

answered Sep 23, 2019 at 15:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gotqn · Accepted Answer · 2019-09-23 15:55:10Z

1

You can use STRING_AGG. Something like this:

SELECT department
      ,top_employee    
      ,STRING_AGG(highest_salary, ',')
FROM findhighest_secondstep 
GROUP BY department
        ,highest_salary

answered Sep 23, 2019 at 15:55

gotqn

44k47 gold badges167 silver badges257 bronze badges

Comments

Tim Biegeleisen · Accepted Answer · 2019-09-23 16:23:33Z

0

We may try using STRING_AGG here, since you are using SQL Server 2017:

WITH cte AS (
    SELECT *, RANK() OVER (PARTITION BY department ORDER BY highest_salary DESC) rnk
    FROM findhighest_secondstep
)

SELECT
    department,
    STRING_AGG(top_employee, ',') AS top_employee,
    MAX(highest_salary) AS highest_salaray
FROM cte
WHERE
    rnk = 1
GROUP BY
    department;

The logic here is that we assign a rank of 1 to every employee in a given department who has the highest salary. Then, we aggregate by department, turning out a CSV list of all employees tied for first place, along with the highest salary.

edited Sep 23, 2019 at 16:23

answered Sep 23, 2019 at 15:56

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

1 Comment

Rajat Over a year ago

Could you help me understand why we need rank() and why max isn't enough. Not sure if I am missing something about the problem.

Collectives™ on Stack Overflow

SQL Server Concatenate Multiple Rows By Group Using FOR XML PATH

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related