How do I turn row data into column data?

Question

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:

Employee Table

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

Report

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!

Pivot in SQL http://msdn.microsoft.com/en-us/library/ms177410.aspx — Magnus, Apr 19 '11 at 19:25
@Magnus It looks like the Pivot in SQL requires an aggregate of some sort, like count/sum/avg. Can someone show how to make the above table transition happen? Even if its using CASE statements. — David, Apr 19 '11 at 21:10
Check this out http://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function — Magnus, Apr 19 '11 at 21:26
I recommend to use XML for this purpose (especially if you need column names). I describe this method [in my blog](http://sql-tricks.blogspot.com/2011/04/sql-server-rows-transpose.html) — Dalex, Apr 20 '11 at 06:52

Michael Fredrickson · Answer 1 · 2011-04-19T20:33:04.473

3

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

enter image description here

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

edited Apr 19 '11 at 20:33

answered Apr 19 '11 at 19:12

Michael Fredrickson

36,839
5
92
109

The Devexpress PivotGrid which I've used several times, seems to want to total something where two or more fields converge. To show the average salary of Males verses Females by Department. (Department set as the "row" field and Sex set as the "column" field, and Salary set as the "data" field of the aspxPivotGrid) I dont know if its possible to have the pivot grid show each field name in the first column and the row values in the subsequent fields as my example shows. – David Apr 19 '11 at 20:13
Can you please show the pivot SQL query using the example table above? – David Apr 19 '11 at 20:18
I guess you're still missing what I'm after. I do not want ANY grouping or totals. The aspxPivot control requires something to total to show in the white area in your screenshot. If you do not give it something to total or to group by the grid shows nothing. I'm not sure its the solution. – David Apr 19 '11 at 21:08
I don't think the SQL pivot function will work either because it also requires something to aggregate. – David Apr 19 '11 at 21:16
Even if you don't want an aggregate, it doesn't hurt anything to have an aggregate. You can just do a MAX()... and it will return the one and only one value that exists for that row... – Michael Fredrickson Apr 19 '11 at 21:25

score 1 · Accepted Answer · answered Apr 20 '11 at 04:04

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;

score 0 · Answer 3 · answered May 30 '14 at 14:34

0

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

answered May 30 '14 at 14:34

Ram

15,908
4
48
41

How do I turn row data into column data?

3 Answers3