How do I turn row data into column data?

I have a simple table of data that needs开发者_开发问答 to be transposed from row data into column data. For example let me create a simple employee table:

I need to create a side-by-side comparison report structured like this using the above sql table:

Can someone show me the sql code using the sample table above? Or can it be done automatically using a built in ASP.net or DevExpress control?

Your feedback is always appreciated!

Thanks!

What you're looking for is a "pivot" function.

You can do them by hand inside of SQL, but it also looks like devexpress has a control for this...

http://www.devexpress.com/Products/NET/Controls/ASP/Pivot_Grid/

-- EDIT --

Like the commenter above posted, here's an introduction to the pivot function in SQL Server... What makes this tricky, is that unless you know exactly what values will comprise your columns, you'll have to use dynamic SQL to build the pivot.

Because it can be a little tricky to do in SQL, I'd try to stick to DevExpress since you already have it...

I think that if you were to add multiple data points to your pivot grid, that it would look like what you're expecting. Here's a screen shot from DevExpress that resembles what you're looking for...

Here's the page that shows this technique... In your case, instead of a row grouping, you could just do a "grand total", and then hide that column...

There it´s using SQL:

WITH FieldValueCte AS(      
SELECT  Name,
        'Department' Field,
        Department Value
FROM    Table1  UNION ALL
SELECT  Name,
        'Sex' Field,
        Sex Value
FROM    Table1  UNION ALL
SELECT  Name,
        'HireDate' Field,
        HireDate Value
FROM    Table1  UNION ALL
SELECT  Name,
        'Salary' Field,
        Salary Value
FROM    Table1  UNION ALL
SELECT  Name,
        'Comments' Field,
        Comments Value
FROM    Table1)

SELECT [Field], [John Doe], [Jane Smith], [Peter Parker], [Jessica James]
FROM
(SELECT Field, Name, Value
    FROM FieldValueCte) AS SourceTable
PIVOT
(
MIN(Value)
FOR Name IN ([John Doe], [Jane Smith], [Peter Parker], [Jessica James])
) AS PivotTable;

The solution is provided here http://www.aspsnippets.com/Articles/Rotate-ASPNet-GridView---Convert-GridView-Columns-to-Rows-and-Rows-to-Columns.aspx