Single row Basic SQL Pivot?

Question

I have a table in a database with rows as follows:

Milk Eggs Butter Cheese Bread 

I want to rotate the entire row into a column as follows:

 Milk Eggs Butter Cheese Bread 

I found many examples using it with multiple columns and rows or based on selection conditions. I have not found examples of how to simply make a single line, and I cannot figure it out. Thanks for the help!

Edit: The tracks below work fine, but is there a way to do this and turn the row value into a column value?

Answer 1

Using the modified version of the sample here .

 DECLARE @SQL varchar(MAX), @ColumnList varchar(MAX) SELECT @ColumnList= COALESCE(@ColumnList + ',','') + QUOTENAME(your_column_name) FROM ( SELECT DISTINCT your_column_name FROM your_table ) T SET @SQL = ' WITH PivotData AS ( SELECT your_column_name FROM your_table ) SELECT ' + @ColumnList + ' FROM PivotData PIVOT ( MAX(your_column_name) FOR your_column_name IN (' + @ColumnList + ') ) AS PivotResult' EXEC (@SQL) 

PS I would have to seriously wonder why I do this. :)

Answer 2

 select your_column_name+' ' from your_table for XML PATH ('') 

Will return

 Milk Eggs Butter Cheese Bread 

Here's the whole script:

 declare @Q as table (name varchar(50)) insert into @Q values ('Milk'), ('Eggs'), ('Butter'), ('Cheese'), ('Bread') select name+' ' from @Q for XML PATH ('') 

Answer 3

Please view this link, you will get some idea

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html