# Puzzle of the Week #11

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```

### Solutions

#1 – Using Recursive WITH clause (Common Table Expression) – to contactenate names

```WITH e AS (
SELECT ename, ROW_NUMBER()OVER(ORDER BY ename) rn, 23 AS maxlen
FROM emp
), x (rn, txt, grp) AS (
SELECT 1, CAST(ename AS VARCHAR2(100)), 1
FROM e
WHERE rn=1
UNION ALL
SELECT e.rn,
CASE WHEN LENGTH(x.txt||','||e.ename)>e.maxlen THEN e.ename
ELSE x.txt||','||e.ename
END,
CASE WHEN LENGTH(x.txt||','||e.ename)>e.maxlen THEN x.grp+1
ELSE x.grp
END
FROM e JOIN x ON e.rn=x.rn+1
)
SELECT MAX(txt) "Names", LENGTH(MAX(txt)) "Length"
FROM x
GROUP BY grp
ORDER BY grp;

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

```

#2 – Using Recursive WITH clause (Common Table Expression) – to group names AND LISTAGG function

```WITH t (ename, len, rn) AS (
SELECT ename, LENGTH(ename) + 1, ROW_NUMBER() OVER(ORDER BY ename)
FROM emp
), r (ename, running_len, rn, gp) AS (
SELECT ename, len, rn, 1
FROM t
WHERE rn = 1
UNION ALL
SELECT t.ename,
CASE WHEN t.len > 24 - r.running_len THEN t.len ELSE r.running_len + t.len END,
t.rn,
r.gp + CASE WHEN t.len > 24 - r.running_len THEN 1 ELSE 0 END
FROM t JOIN r ON t.rn = r.rn + 1
)
SELECT LISTAGG(ename, ',') WITHIN GROUP(ORDER BY rn) AS "Names", MAX(running_len) - 1 AS "Length"
FROM r
GROUP BY gp
ORDER BY gp
/
```

#3: Using Recursive WITH clause (CTE) – to group names in a different way

```WITH data (ename, grp, pass) AS (
SELECT ename,
CASE WHEN SUM(LENGTH(ename) + 1) OVER(ORDER BY  ename) - 1 <= 23
THEN 1
ELSE 0
END, 1
FROM emp
UNION ALL
SELECT ename,
CASE WHEN SUM(LENGTH(ename) + 1) OVER (ORDER BY  ename) - 1 <= 23
THEN 1
END, pass + 1
FROM data
WHERE (grp = 0 AND pass = 1) OR grp IS NULL
), x AS (
SELECT LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS names, pass
FROM data
WHERE grp = 1
GROUP BY pass
)
SELECT names "Names", LENGTH(names) AS "Length"
FROM x
ORDER BY 1;```

#4: Using XMLAGG with Regular Expressions

```WITH t AS (
SELECT TRIM(',' FROM XMLAGG(xmlelement(e, ename||',') ORDER BY ename).EXTRACT('//text()')) AS txt
FROM  emp
), x AS (
SELECT LEVEL AS l,
TRIM(',' FROM TRIM(REGEXP_SUBSTR(txt,'.{1,23}(,|\$)',1,LEVEL))) AS names
FROM t
CONNECT BY TRIM(',' FROM TRIM(REGEXP_SUBSTR(txt,'.{1,23}(,|\$)',1,LEVEL))) IS NOT NULL
)
SELECT names "Names", LENGTH(names) "Length"
FROM x
/
```

#5: Using LISTAGG with Regular Expressions

```WITH  x AS (
SELECT LISTAGG (ename, ',') WITHIN GROUP (ORDER BY 1) str
FROM emp
)
SELECT RTRIM(REGEXP_SUBSTR (str, '.{1,23}(,|\$)', 1, LEVEL), ',')  "Names",
LENGTH(RTRIM(REGEXP_SUBSTR (str, '.{1,23}(,|\$)', 1, LEVEL), ',')) "Length"
FROM x
CONNECT BY RTRIM(REGEXP_SUBSTR (str, '.{1,23}(,|\$)', 1, LEVEL), ',') IS NOT NULL```

#6: Using MODEL clause for grouping names

```WITH m AS (
SELECT i, ename, grp, len, prevlen
FROM emp
MODEL
DIMENSION BY (ROW_number() OVER (ORDER BY  ename) AS i)
MEASURES
(
ename AS ename,
CAST('' AS VARCHAR2(24)) AS names,
0 AS grp,
0 AS len,
0 AS prevlen
)
RULES
(
len[i] = LENGTH(ename[CV()]),
prevlen[i] = CASE WHEN (CASE WHEN NVL(prevlen[CV()-1],0) = 0 THEN NVL(len[CV()-1],0)
ELSE NVL(prevlen[CV()-1],0) + 1 +  NVL(len[CV()-1],0)
END) > 23
THEN NVL(len[CV()-1],0)
ELSE CASE WHEN NVL(prevlen[CV()-1],0) = 0 THEN NVL(len[CV()-1],0)
ELSE NVL(prevlen[CV()-1],0) + 1 +  NVL(len[CV()-1],0)
END
END,
grp[i] = NVL(grp[CV()-1],0) + CASE WHEN prevlen[CV()+1] < prevlen[CV()] THEN 1 ELSE 0 END
)
)
SELECT LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename) AS "Names" , LENGTH(listagg(ename,',') WITHIN GROUP (ORDER BY  ename)) AS "Length"
FROM m
GROUP BY grp;

```

#7: Oracle 12c Solution – Using MATCH_RECOGNIZE clause

```SELECT  LISTAGG(name,',') WITHIN GROUP(ORDER BY name) "Names",
LENGTH(LISTAGG(name,',') WITHIN GROUP(ORDER BY name)) "Length"
FROM  EMP
MATCH_RECOGNIZE
(
ORDER BY ENAME
MEASURES
MATCH_NUMBER() rn,
UP.ENAME name
ALL ROWS PER MATCH
PATTERN (UP+)
DEFINE
UP AS SUM(LENGTH(UP.ENAME || ',')) <= 24
)
GROUP BY RN
ORDER BY RN

```

### 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.