17

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

jam
  • 3,640
  • 5
  • 34
  • 50
Master
  • 415
  • 2
  • 4
  • 11

3 Answers3

15

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.

Octavian Theodor
  • 537
  • 6
  • 14
mutlu
  • 151
  • 1
  • 5
  • "each row represents a particular attribute of "persons" and when you select a particular row, you get that particular attribute of ALL the people" -- I think you actually meant "each column" and "particulat column"... I took the liberty of changing the wording. Otherwise, a very nice example, indeed -- thanks! – Octavian Theodor Apr 28 '17 at 09:10
10

Usually, it's because the layout works better in a report, or user interface after pivoting.

Data on a normalized database may result in data in the "shape" of multiple records, where the UI wants to see those records as fields. Data normalization is great, but when it comes to representing data in a concise, natural format, pivoting is often necessary.

example here.

Granted, the example I linked to is Excel, not a true DB, but the pictures describe an example of where the pivoted data looks more natural, and the concept is the same.

David
  • 72,686
  • 18
  • 132
  • 173
  • Thanks ! By looking at that example, I can see one possible use of pivoting - Suppose we want to add 3 new products Prod G, Prod H, Prod I. By using the pivoted way (GI table) we can add new products by simply increasing the rows instead of increasing the number of columns. Thus, there is no need to add new columns. Of course, this requires more memory. But, I wonder who would want to use the pivot and why ? – Master Jul 24 '12 at 06:10
2

How about this as an example?

How to PIVOT Data Using T-SQL

A common expectation in data extraction is the ability to transform the output of multiple rows into multiple columns in a single row. SQL Server 2005/2008 provide the ability to do this with the PIVOT operator in a Query.

EDIT

Lets say you have a table that stores sales per customer by date

Something like

Table
- SaleDate
- CustomerID
- SaleAmount

Using PIVOT you can display a grid that totals sales per client by month/quarter/year

    |Client A|Client B|Client C
--------------------------------
2007| 100    | 0      | 150
2008| 0      | 200    | 160
2009| 110    | 180    | 100

This would purely be for summary purposes.

Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • Indeed, that is the definition of pivoting. Why would someone want to convert the columns into rows ? What is the purpose ? – Master Jul 24 '12 at 05:39