## Puzzle of the Week #11

Puzzle: Produce the Employee Roll Report that satisfies the following list of requirements:

• Use single SELECT statement
• Single column “Names” should have a list of the employee names separated by comma
• The maximum size of the values in the “Names” column should be 23
• The report should have as few rows as possible
• All the employee names should be concatenated in the alphabetical order

Expected Result: (the Length column is added for length verification only)

```Names                                        Length
---------------------------------------- ----------
FORD,JAMES,JONES,KING                            21
MARTIN,MILLER,SCOTT                              19
SMITH,TURNER,WARD                                17```

A correct answer (and workarounds!) will be published here in a week.

### My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

### Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

### 15 thoughts on “Puzzle of the Week #11”

1. Krishna Jamal May 9, 2016 / 2:57 pm
```SELECT WM "Emp_Name", LENGTH(WM) "Length"
FROM(SELECT DISTINCT WM_CONCAT(Ename) OVER(PARTITION BY Ntl) WM
FROM(SELECT Ename, NTILE(4) OVER(ORDER BY Ename) Ntl FROM Emp))
ORDER BY 2 DESC;
```
• Zahar Hilkevich May 9, 2016 / 3:10 pm

You do not limit each line by 23 characters or less which is one of the requirements

2. sajith May 10, 2016 / 10:57 am
```with x as (
select
trunc((SUM(length(ename)) OVER (order by ename)/23)) len_name_div
,ename
from emp
)
select LISTAGG(ename, ',') WITHIN GROUP (ORDER BY len_name_div) as LISTAGG_OUTPUT
from x
group by len_name_div;
```
• Zahar Hilkevich May 10, 2016 / 11:43 am

The result is wrong:

```NAMES                                 Len
------------------------------ ----------
FORD,JAMES,JONES,KING,MARTIN           28  <== over 23
MILLER,SCOTT,SMITH,TURNER              25  <== over 23
WARD                                    4

```
3. Amarjot May 11, 2016 / 6:13 am

with tmp(a) as (select ‘ADAMS’ from dual union all
select ‘ALLEN’ from dual union all
select ‘BLAKE’ from dual union all
select ‘CLARKFORD’ from dual union all
select ‘JAMES’ from dual union all
select ‘JONES’ from dual union all
select ‘KINGMARTIN’ from dual union all
select ‘MILLER’ from dual union all
select ‘SCOTT’ from dual union all
select ‘SMITH’ from dual union all
select ‘TURNER’ from dual union all
select ‘WARD’ from dual
)
select listagg(a,’,’) within group (order by a) grping_strg,
length(listagg(a,’,’) within group (order by a)) from (
select
a,
floor((sum(length(a) + 1) over (order by a))/23) grp
from tmp
)
group by grp

• Zahar Hilkevich May 17, 2016 / 10:21 pm
```with tmp(a) as (select ename from emp)
select listagg(a,',') within group (order by a) "Names",
length(listagg(a,',') within group (order by a)) "Length"
from (
select
a,
floor((sum(length(a) + 1) over (order by a))/23) grp
from tmp
)
group by grp;

Names                              Length
------------------------------ ----------
CLARK,FORD,JAMES,JONES                 22
KING,MARTIN,MILLER,SCOTT               24  <==
SMITH,TURNER,WARD                      17
```
4. Krishna Jamal May 11, 2016 / 2:28 pm

WITH x AS (
SELECT Ename, CEIL(LENGTH(WM_CONCAT(Ename) OVER(ORDER BY Ename))/23) nt FROM Emp
)
SELECT DISTINCT WM_CONCAT(Ename) OVER(PARTITION BY nt) AS Names FROM x;

• Krishna Jamal May 12, 2016 / 2:52 am

WITH x AS(
SELECT LISTAGG(Ename, ‘,’) WITHIN GROUP(ORDER BY Ename) AS Names
FROM (SELECT Ename, CEIL(LENGTH(WM_CONCAT(Ename) OVER(ORDER BY EName))/23) nt FROM Emp)
GROUP BY nt)
SELECT Names, LENGTH(Names) “Length” FROM x;

• Zahar Hilkevich May 17, 2016 / 10:24 pm

Changing 23 to 20 reveals the problem in a query:

```WITH x AS(
SELECT LISTAGG(Ename, ',') WITHIN GROUP(ORDER BY Ename) AS Names
FROM (SELECT Ename, CEIL(LENGTH(WM_CONCAT(Ename) OVER(ORDER BY EName))/20) nt FROM Emp)
GROUP BY nt)
SELECT Names, LENGTH(Names) "Length" FROM x;

NAMES                              Length
------------------------------ ----------
CLARK,FORD,JAMES,JONES                 22
KING,MARTIN,MILLER                     18
SCOTT,SMITH,TURNER                     18
WARD                                    4

```
5. stewashton May 13, 2016 / 9:05 am

Requires Oracle Database version 12c:

select listagg(ename, ‘,’) within group(order by ename) as “Names”,
length(listagg(ename, ‘,’) within group(order by ename)) as “Length”
from scott.emp
match_recognize(
order by ename
measures match_number() mn
all rows per match
pattern(a+)
define a as sum(length(ename))+count(*) – 1 <= 23
)
group by mn;

• Zahar Hilkevich May 17, 2016 / 10:47 pm

Very good! Do you have a Facebook account so I could reference you as a winner of this Puzzle of the Week Contest?

6. jolivercomblog May 13, 2016 / 3:13 pm

Hello, sorry to use this post for an alternative reason. However, Mr. Hilkevich can you suggest a good PL/SQL book? I would need a book that would guide me from beginner to intermediate/advanced. I tried to find an email address for you but was not able to. Lastly, I just joined your blog and purchased your book a few weeks back. Are there any plans to produce a Kindle version of your book? thanks in advance, JamesO

• Zahar Hilkevich May 17, 2016 / 10:27 pm

Oracle PL/SQL by Example is a good one

7. jolivercomblog May 13, 2016 / 3:58 pm

SELECT LISTAGG (LNAME, ‘,’)
WITHIN GROUP (ORDER BY LNAME)AS NAMES,
LENGTH(LISTAGG (LNAME, ‘,’)
WITHIN GROUP (ORDER BY LNAME))AS Length

FROM
(
SELECT LNAME, NTILE(4) OVER (ORDER BY LNAME)GROUP_IT
FROM EMPLOYEE)X
GROUP BY GROUP_IT