Menu
Why would someone want to convert columns into rows (ie pivoting)? Which problem does it solve?
I found some links, but did not get satisfactory answers to the questions.
http://codeghar.wordpress.com/2007/11/03/pivot-tables/
http://msdn.microsoft.com/en-us/library/ms177410%28SQL.105%29.aspx
870
Moving rows to columns or columns to rows (or "pivoting") to see different summaries of the source data. Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data enabling you to focus on just the information you want.
Pivoting data is a technique that rotates data from a state of rows to a state of columns, possibly aggregating multiple source values into the same target row and column intersection.
PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. And PIVOT runs aggregations where they're required on any remaining column values that are wanted in the final output.
Another important benefit of pivot tables is that it helps to summarize data in a quick and easy manner. The table helps in making a concise summary out of thousands of rows and columns of unorganized data. With the help of these tables you can summarize large amounts of information into a small space.
As regards to your question " Why would someone want to convert columns into rows (ie pivoting)? Which problem does it solve?"
It's not a matter of "how data looks visually" but rather "how it is to be organized and handled". Namely:
In most of the databases, rows represent "Records - (entity, event, etc)" and columns "Fields (attributes of that entity)". For instance the below is a typical DB representation of data;
Person | Birth | Height |
-------------------------------
John | 1980 | 1.82 |
Smith | 1987 | 2.02 |
That means; each column represents a particular attribute of "persons" and when you select a particular column, you get that particular attribute of ALL the people. This way, a column is a "Dimension" and all values have the same unit (data type), all are dates, all are lengths, etc.
In financial modeling, however, it is just much convenient to represent data the other way around. For instance a typical in "Monthly Cash Flow" table looks like this.
Item | Jan | Feb |
-----------------------------
Sales | $100 | $150 |
Tax | -$50 | -$15 |
Note that this kind tabulation in a spreadsheet does NOT adhere database formats, column heading are time, but the values in the columns are monetary values, CONFLICT, we can't do calculations with this columns.
If we PIVOT this table, it becomes
Date | Sales | Tax |
------------------------------
Jan | $100 | -$50 |
Feb | $150 | -$15 |
Now, column dimensions (heading) and the data inside them are consistent. Date column have all dates, and the others have all $ figures. We can take a column and do vector operations with it.
This is one problem that pivoting solves.
87
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
