I have a table in a database with rows as follows:
Milk
Eggs
Butter
Cheese
Bread
I want to pivot the entire row into a column appears as follows:
Milk Eggs Butter Cheese Bread
I have found a lot of examples using it with multiple columns and rows or based on select conditions. I haven't found any examples of how to just do one row, and I can't figure it out. Thanks for the help!
Ed开发者_JAVA技巧it: The ways below work great but is there a way to do it and turn the row value into a column value?
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 ('')
Using a 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)
P.S. I'd have to seriously question why I was doing this. :)
精彩评论