[SOLVED] Dynamic Statement to Get Top "n" for Each Individual Column from Ms SQL Table

Dynamic Statement to Get Top "n" for Each Individual Column from Ms SQL Table

Help me with this Dynamic statement perform faster, the statement will fetch top n values for each column from a table.

The table will have an "n" number of columns but will have a primary key. NULLs couldn't have been avoided as any other value is considered as VALID and should go to the database.

Table

+-------+------+------+------+
| Depth | RPMA | ROP  | WOB  |
+-------+------+------+------+
|  6111 |   72 | 14.6 | 0    |
|  6110 |   72 | 14.1 | 1    |
|  6109 |   66 | 15.2 | NULL |
|  6108 |   68 | 14   | NULL |
|  6107 |   69 | 14   | NULL |
|  6106 |   61 | 14.8 | NULL |
|  6105 |   70 | NULL | NULL |
|  6104 |   64 | NULL | NULL |
|  6103 |   59 | NULL | NULL |
|  6102 |   49 | NULL | NULL |
+-------+------+------+------+

Result set,

+-------+------+------+------+
| Depth | RPMA | ROP  | WOB  |
+-------+------+------+------+
|  6111 | 72   | NULL | 0    |
|  6110 | 72   | NULL | 1    |
|  6109 | NULL | 15.2 | NULL |
|  6106 | NULL | 14.8 | NULL |
+-------+------+------+------+

Dynamic SQL used to get current result set,

DECLARE @Columns VARCHAR(MAX); -- Param1
DECLARE @IdxColumn VARCHAR(250); --Param2
DECLARE @Limit VARCHAR(11); --Param3
DECLARE @SQL NVARCHAR(MAX)=''; --Param4

DECLARE @query NVARCHAR(MAX) = ' SELECT TOP (' + @pLimit + ') ' + @IdxColumn + ', ' + @Columns + ' FROM [Table] WHERE '

SET @SQL = @query + REPLACE(@Columns,',', ' IS NOT NULL ORDER BY '+ @IdxColumn + ' ASC ' + N' UNION' + @query) + ' IS NOT NULL ORDER BY ' + @IdxColumn

SET @SQL = 'SELECT * FROM ('+@SQL+') T ORDER BY ' + @IdxColumn + ' ASC'   

EXEC (@SQL)

Solution

The following query should work for the sample data:

WITH cte AS (
  SELECT *
       , DENSE_RANK() OVER (ORDER BY RPMA DESC) AS RPMA_RANK
       , DENSE_RANK() OVER (ORDER BY ROP DESC) AS ROP_RANK
       , DENSE_RANK() OVER (ORDER BY WOB DESC) AS WOB_RANK
  FROM t
)
SELECT Depth
     , CASE WHEN RPMA_RANK <= 2 THEN RPMA END
     , CASE WHEN ROP_RANK <= 2 THEN ROP END
     , CASE WHEN WOB_RANK <= 2 THEN WOB END
FROM cte
WHERE RPMA_RANK <= 2
OR ROP_RANK <= 2
OR WOB_RANK <= 2

Note that it returns three rows for RPMA column (there are two 72 and one 70). For n number of columns you need to use dynamic SQL.