SQL query pivot, move values to top

I’ve created a PIVOT query and the results are fine, however. I would like to flatten the rows so to speak and move all values to the top of the list and NULLS to the bottom.

http://sqlfiddle.com/#!18/7d17d/6

Instead of this:

+----------+----------+----------+----------+----------+
|    A     |    B     |    C     |    D     |    E     |
+----------+----------+----------+----------+----------+
| Report 1 | (null)   | (null)   | (null)   | (null)   |
| (null)   | Report 1 | (null)   | (null)   | (null)   |
| (null)   | (null)   | Report 2 | (null)   | (null)   |
| Report 3 | (null)   | (null)   | (null)   | (null)   |
| (null)   | (null)   | Report 4 | (null)   | (null)   |
| (null)   | Report 4 | (null)   | (null)   | (null)   |
| (null)   | (null)   | (null)   | Report 4 | (null)   |
| (null)   | (null)   | (null)   | (null)   | Report 5 |
| (null)   | Report 6 | (null)   | (null)   | (null)   |
| (null)   | (null)   | (null)   | (null)   | Report 7 |
| (null)   | (null)   | (null)   | (null)   | Report 8 |
+----------+----------+----------+----------+----------+

I would like to create something like this:

+----------+----------+----------+----------+----------+
|    A     |    B     |    C     |    D     |    E     |
+----------+----------+----------+----------+----------+
| Report 1 | Report 1 | Report 4 | Report 4 | Report 5 |
| Report 3 | Report 4 | Report 2 | (null)   | Report 7 |
| (null)   | Report 6 | (null)   | (null)   | Report 8 |
+----------+----------+----------+----------+----------+

Answer

Change your ROW_NUMBER() to partition by the data source:

ROW_NUMBER() OVER (partition by datasource ORDER BY GETDATE()) AS rn

Your version is assigning a unique number to every row in the source data, which is why you get that number of rows back.

So the query is:

set @query = 'SELECT ' + @cols + '
from (select Name, DataSource,
             row_number() over (partition by datasource order by getdate()) AS rn
      from T
     ) x
pivot (max(Name) for DataSource in (' + @cols + ')
     ) p '

execute(@query);

Here is the fiddle.