NTILE vs WIDTH_BUCKET and the Winner is...PIVOT

At a recent meeting of developers, a requirement was discussed where a finite number of columns are to be populated in alphabetical order - think the opposite of telephone book.

As is often the case, there are multiple ways to do this, but at it's heart, this really is a pure pivot. I think we should rule out both NTILE and WIDTH_BUCKET.

NTILE creates equiheight histograms, while WIDTH_BUCKET creates equiwidth histograms. We could probably force the use of either, but we would still need to pivot based on the quartile values.

NTILE, in reality, allows us to specify the number of rows not the number of columns across. For example,

SELECT ename , NTILE(5) OVER ( ORDER BY ename ) AS quartile FROM emp

ENAME	QUARTILE
ADAMS	1
ALLEN	1
BLAKE	1
CLARK	2
FORD	2
JAMES	2
JONES	3
KING	3
MARTIN	3
MILLER	4
SCOTT	4
SMITH	4
TURNER	5
WARD	5

Modifying the query actually adds to the number of columns. For example,

SELECT ename , NTILE(3) OVER ( ORDER BY ename ) AS quartile FROM emp

ENAME	QUARTILE
ADAMS	1
ALLEN	1
BLAKE	1
CLARK	1
FORD	1
JAMES	2
JONES	2
KING	2
MARTIN	2
MILLER	2
SCOTT	3
SMITH	3
TURNER	3
WARD	3

At first glance, WIDTH_BUCKET would seem to make better sense, because we want to specify a number of columns, but these columns are assigned based on numerical ranges, not strings.

So the most straightforward way to do this, I feel, is a pure PIVOT...something like this.

SELECT col_1 , col_2 , col_3 

  FROM  ( SELECT ename ,

                 TRUNC ( ( ROW_NUMBER() OVER ( ORDER BY ename ) - 1 ) / 3 ) AS row_num ,

                     MOD ( ROW_NUMBER() OVER ( ORDER BY ename ) - 1, 3 )    AS col_num 

            FROM emp ) PIVOT

          ( MAX(ename) FOR col_num IN ( 0 as col_1 , 1 as col_2 , 2 as col_3 )

        )

ORDER BY row_num

COL_1	COL_2	COL_3
ADAMS	ALLEN	BLAKE
CLARK	FORD	JAMES
JONES	KING	MARTIN
MILLER	SCOTT	SMITH
TURNER	WARD

To display 4 columns instead of 3....

SELECT col_1 , col_2 , col_3 , col_4

  FROM  ( SELECT ename ,

                 TRUNC ( ( ROW_NUMBER() OVER ( ORDER BY ename ) - 1 ) / 4 ) AS row_num ,

                     MOD ( ROW_NUMBER() OVER ( ORDER BY ename ) - 1, 4 )    AS col_num 

            FROM emp ) PIVOT

          ( MAX(ename) FOR col_num IN ( 0 as col_1 , 1 as col_2 , 2 as col_3 , 3 as col_4 )

        )

ORDER BY row_num

COL_1	COL_2	COL_3	COL_4
ADAMS	ALLEN	BLAKE	CLARK
FORD	JAMES	JONES	KING
MARTIN	MILLER	SCOTT	SMITH
TURNER	WARD

To completely eliminate the PIVOT, one could use the MODEL clause, but that can get pretty tedious, pretty fast.

SELECT col_1, col_2, col_3

  FROM   emp

 MODEL

  RETURN UPDATED ROWS

  DIMENSION BY

  ( TRUNC ( ( ROW_NUMBER() OVER ( ORDER BY ename ) - 1 ) / 3 ) AS row_num ,

        MOD ( ROW_NUMBER() OVER ( ORDER BY ename ) - 1, 3 )    AS col_num

  )

  MEASURES ( ename AS col_1, ename AS col_2, ename AS col_3 )

  RULES UPSERT

...not this kind of MODEL

  (

    col_2[ANY,0] = col_1[CV(),1] ,

    col_3[ANY,0] = col_1[CV(),2]

  )

ORDER BY row_num 

COL_1	COL_2	COL_3
ADAMS	ALLEN	BLAKE
CLARK	FORD	JAMES
JONES	KING	MARTIN
MILLER	SCOTT	SMITH
TURNER	WARD

...our journey continues