sql - Rearranging values from a pivot table -

i using sql server 2012, self-taught sorry errors/mistakes.

i have table had pivot , resulting table has null values so:

pepsi    coke    gatorade -------------------------- vanilla  null    null cherry   null    null null     vanilla null null     cherry  null null     null    lime null     null    fruit punch null     null    grape 

my question if there way rearrange table rows not null show @ top of respective columns in such way if user add different type of drink, such water, query automatically sort newly added column. if cannot accomplished alternative suggestion appreciated.

an example of desired result so:

pepsi    coke    gatorade ------------------------------ vanilla  vanilla lime cherry   cherry  fruit punch null     null    grape 

i have tried doing series of outer joins from clause however, cannot figure out way generate results without explicitly calling columns and/or table names.

select distinct t1.pepsi, t2.coke  (select      #test.pepsi pepsi,      row_number() on (order pepsi) r   #test  pepsi not null) t1  full outer join  (select      #test.coke coke,      row_number() on (order coke) r   #test   coke not null) t2  on t1.r=t2.r  

the original table looks so:

drink    type     price   location  etc coke     vanilla  coke     cherry gatorade lime gatorade grape . . . pepsi    cherry 

any advice or appreciated.

declare @t table ([drink] varchar(max), [type] varchar(max), [price] money )  insert @t ([drink],[type]) values ('coke','vanilla'), ('coke','cherry'), ('gatorade','lime'), ('gatorade','grape'), ('gatorade','fruit punch'), ('pepsi','vanilla'), ('pepsi','cherry')  ;with t (   select     [drink],[type],     row_number() over(partition [drink] order [type] desc) rn   @t ) select [pepsi],[coke],[gatorade] t pivot(max([type]) [drink] in ([pepsi],[coke],[gatorade])) p