How to create Percentile Table format

DECLARE @forumTable TABLE (value float)

WHILE (SELECT COUNT(*) FROM @forumTable) < 50
BEGIN
 INSERT INTO @forumTable (value)
 VALUES  (ROUND(((20 - 0- 1)*rand())+0,2))
END

;WITH pTile AS (
SELECT MAX(value) AS pTileUpper, MAX(value)-(MAX(value)/10) AS pTileLower, 10 AS pTile
  FROM @forumTable
UNION ALL
SELECT (pTileUpper/10)*(pTile-1), (pTileUpper/10)*(pTile-2), pTile - 1 AS pTile
  FROM ptile
 WHERE pTile - 1 >= 1
)

SELECT COUNT(*) AS cnt, pTile, pTile*10 AS pct, pTileLower, pTileUpper
  FROM @forumTable f
    INNER JOIN pTile p
          ON f.value BETWEEN p.pTileLower AND p.pTileUpper
 GROUP BY pTile, pTileLower, pTileUpper
 ORDER BY pTile

I think this is something like what you want.

First I set up a demo table to hold some random data for testing/demo. It just puts a bunch of random floats ranging from 0 to 20 in a table.

Using that table, we use an rCTE to create a percentile lookup table, based on 10% increments.

Finally, we can now join the percentile lookup to our table and group by the percentiles to display the following results:

cnt    pTile           pct     pTileLower      pTileUpper
1       4               40      0.8468712       1.1291616
1       5               50      2.2583232       2.822904
5       7               70      8.06544         9.40968
10      8               80      11.7621         13.4424
4       9               90      14.936          16.803
6       10              100     16.803          18.67