Transpose row results to single row

Question

Perhaps my example is too simple but I need to transpose the results from being multiple rows to being multiple columns in a single row. The only issue that the number of returned initial rows may vary so therefore my final number of columns may vary also.

As an example, my returned results from

select name from pets

could be:

Dog

Cat

Fish

Rabbit

And I need each value in a separate column:

Dog Cat Fish Rabbit

It is probably better to do this transformation in the application, rather than in the database. — Gordon Linoff, Jun 01 '15 at 01:45
possible duplicate of [Is there a way to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field?](http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-sql-server-function-to-join-multiple-rows-from-a-su) — TimoStaudinger, Jun 01 '15 at 01:47
The concept is called pivoting, plenty of questions answered on SO. However, SQL Server does not allow dynamic pivoting. Anecdotally, MS Access does (using the TRANSFORM statement) — Chris Bednarski, Jun 01 '15 at 02:06

score 1 · Accepted Answer · answered Jun 01 '15 at 06:26

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX)
-- First create list of columns that you need in end result
SET @columns = N''
SELECT @columns += N', ' + QUOTENAME(name)
FROM (select distinct name from pets) AS x     


-- now create pivot statement as:

SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT name
   FROM pets   
) AS j
PIVOT
(
  max(name) FOR name IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;'


EXEC sp_executesql @sql;

DEMO

Transpose row results to single row

1 Answers1