开发者

One Line Basic SQL Pivot?

开发者 https://www.devze.com 2023-04-03 19:16 出处:网络
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:

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. :)

0

精彩评论

暂无评论...
验证码 换一张
取 消