CODEWITHSUNDEEP
sqlsql-server-2008
Goolsy
Goolsy

Reputation: 237

Transpose row results to single row

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

Upvotes: 0

Views: 238

Answers (1)

Deepshikha
Deepshikha

Reputation: 10274

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

Upvotes: 1

Related Questions