sql - Transpose row results to single row -

perhaps example simple need transpose results being multiple rows being multiple columns in single row. issue number of returned initial rows may vary therefore final number of columns may vary also.

as example, returned results

select name pets 

could be:

dog

cat

fish

rabbit

and need each value in separate column:

dog cat fish rabbit

declare @columns nvarchar(max), @sql nvarchar(max) -- first create list of columns need in end result set @columns = n'' select @columns += n', ' + quotename(name) (select distinct name pets) x        -- create pivot statement as:  set @sql = n' select ' + stuff(@columns, 1, 2, '') + ' (   select name    pets    ) j pivot (   max(name) name in ('   + stuff(replace(@columns, ', p.[', ',['), 1, 1, '')   + ') ) p;'   exec sp_executesql @sql; 

demo