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
---------------------------------------- ----------
ADAMS,ALLEN,BLAKE,CLARK                          23
FORD,JAMES,JONES,KING                            21
MARTIN,MILLER,SCOTT                              19
SMITH,TURNER,WARD                                17

To submit your answer (one or more!) please start following this blog and add a comment to this post.

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.

Advertisements

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

  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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s