Concatenate multiple strings ordered (PIVOT)

Question

I'm trying to concatenate the names of multiple Employees (ordered) based on their hierarchical level, which ranges between 0 and 4, without duplicates. The issue itself is fairly easy to solve (although I used Group By when a PIVOT would most likely be ideal), however, when I try to use STRING_AGG with ordering statements, Synapse Analytics says I cannot order by all of these, I get the error:

Multiple ordered aggregate functions in the same scope have mutually incompatible orderings.

For instance, consider the following data:

Department  | Employee Name  | Level
----------------------------------------
     A        |     John          |   0
     A        |    Andrew         |   0
     A        |   McKenzee        |   1
     A        |    Alissa         |   2
     B        |   Catherine       |   0
     B        |      Jack         |   1
     B        |      Jack         |   1
     B        |      Luanne       |   1
     B        |      Abigail      |   2

The expected output would be:

Department    |     Level 0           | Level 1           | Level 2
--------------------------------------------------------------------
     A        |     Andrew, John      |   McKenzee        | Alissa
     B        |     Catherine         |   Jack, Luanne    | Abigail

However, doing so results in the error above:

select
    ...
from
(
select
    department
    ,STRING_AGG(level_0, ',') WITHIN GROUP (ORDER BY level_0) level_0
    ,STRING_AGG(level_1, ',') WITHIN GROUP (ORDER BY level_1) level_1
    ,STRING_AGG(level_2, ',') WITHIN GROUP (ORDER BY level_2) level_2
    ,STRING_AGG(level_3, ',') WITHIN GROUP (ORDER BY level_3) level_3
  from
  (
    select
      department
      ,case 
        when employee_level = 0 then employee_name
      end level_0
      ,case 
        when employee_level = 1 then employee_name
      end level_1
      ,case 
        when employee_level = 2 then employee_name
      end level_2
      ,case 
        when employee_level = 3 then employee_name
      end level_3
      ,case 
        when employee_level = 4 then employee_name
      end level_4
    from
      employee_records
    group by
         department
  ) base_
  group by
    department
) le_subquery

Note: Since this is actually a subquery within a higher query I cannot use the Order By statement.

While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question as text, no images. — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Mar 28, 2023 at 21:25

Charlieface · Accepted Answer · 2023-03-28 22:18:34Z

2

You can just use conditional aggregation in a STRING_AGG

SELECT
  Department,
  STRING_AGG(CASE WHEN Level = 0 THEN Employee END, ',') WITHIN GROUP (ORDER BY Employee) level_0,
  STRING_AGG(CASE WHEN Level = 1 THEN Employee END, ',') WITHIN GROUP (ORDER BY Employee) level_1,
  STRING_AGG(CASE WHEN Level = 2 THEN Employee END, ',') WITHIN GROUP (ORDER BY Employee) level_2,
  STRING_AGG(CASE WHEN Level = 3 THEN Employee END, ',') WITHIN GROUP (ORDER BY Employee) level_3
FROM @tbl
GROUP BY Department;

db<>fiddle

answered Mar 28, 2023 at 22:18

Charlieface

78.9k8 gold badges35 silver badges77 bronze badges

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

2 Comments

Yitzhak Khabinsky Over a year ago

Eventually, I came up with the same solution just using IIF instead of CASE statement.

Charlieface Over a year ago

Yeah basically the same (it even compiles the same) just CASE is more standardized and well-known.

Collectives™ on Stack Overflow

Concatenate multiple strings ordered (PIVOT)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related