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 | +----------+----------+----------+----------+----------+
Advertisement
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.