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
---------------------------------------- ----------
ADAMS,ALLEN,BLAKE,CLARK 23
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
------------------------- ----------
ADAMS,ALLEN,BLAKE,CLARK 23
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.
sqlpatterns 