Summarizing the database - what is the purpose?

Question

Why does someone want to convert columns to rows (i.e. collapse)? What problem does he solve?

I found several 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

Answer 1

This is usually because the layout works better in the report or user interface after the rotation.

Data about a normalized database can lead to data in the "form" of several records, where the user interface wants to see these records as fields. Data normalization is great, but when it comes to presenting data in a compressed, natural format, a turn is often required.

an example here .

Of course, the example I'm attached to is Excel, not the true DB, but the figures show an example of where the rotational data looks more natural and the concept is the same.

Answer 2

As for your question, “Why does someone want to convert columns to rows (i.e. rotation)? What problem does he solve?”

This is not a question of “how data looks visually”, but rather “how to organize and process it.” Namely:

In most databases, the rows represent "Records - (entity, event, etc.)" and the "Fields (attributes of this object)" columns. For example, the following is a typical representation of data in DB format;

Person | Birth | Height | ------------------------------- John | 1980 | 1.82 | Smith | 1987 | 2.02 | 

It means; each column represents a special attribute of "persons", and when you select a particular column, you get this special attribute of ALL people. Thus, the column is “Size”, and all values ​​have the same unit (data type), all are dates, all are lengths, etc.

In financial modeling, however, it is much more convenient to present data in a different way. For example, a typical Monthly Cash Flow table looks like this.

 Item | Jan | Feb | ----------------------------- Sales | $100 | $150 | Tax | -$50 | -$15 | 

Please note that such a tabulation table in a spreadsheet does NOT support database formats, the column heading is time, but the values ​​in the columns are monetary values, CONFLICT, we cannot perform calculations with these columns.

If pivot this table, it becomes

 Date | Sales | Tax | ------------------------------ Jan | $100 | -$50 | Feb | $150 | -$15 | 

Now the dimensions of the column (header) and the data inside them are consistent. The date column has all the dates, and the rest has all the numbers. We can take a column and perform vector operations with it.

This is one of the problems that solves the turn.

Answer 3

How about this as an example?

How to use PIVOT data using T-SQL

A common expectation when extracting data is the ability to convert the output of multiple rows to multiple columns on the same row. SQL Server 2005/2008 provides the ability to do this using the PIVOT statement in the query.

EDIT

Suppose you have a table that stores sales for each customer by date.

Something like

 Table - SaleDate - CustomerID - SaleAmount 

Using PIVOT, you can display a grid that reflects sales for each customer by month / quarter / year.

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

That would be purely for short purposes.