pivot

How to transform vertical data into horizontal data with SQL?

Regardless of the database you are using, the concept of what you are trying to achieve is called “Pivot Table”. Here’s an example for mysql: http://en.wikibooks.org/wiki/MySQL/Pivot_table Some databases have builtin features for that, see the links below. SQLServer: http://msdn.microsoft.com/de-de/library/ms177410.aspx Oracle: http://www.dba-oracle.com/t_pivot_examples.htm You can always create a pivot by hand. Just select all the aggregations in … Read more

SQL server join tables and pivot

This should work: WITH Sales AS ( SELECT S.SaleID, S.SoldBy, S.SalePrice, S.Margin, S.Date, I.SalePrice, I.Category FROM dbo.Sale S INNER JOIN dbo.SaleItem I ON S.SaleID = I.SaleID ) SELECT * FROM Sales PIVOT (Max(SalePrice) FOR Category IN (Books, Printing, DVD)) P ; Or alternately: SELECT S.SaleID, S.SoldBy, S.SalePrice, S.Margin, S.Date, I.Books, I.Printing, I.DVD FROM dbo.Sale S … Read more

Difference between pivot and pivot_table. Why is only pivot_table working?

For anyone who is still interested in the difference between pivot and pivot_table, there are mainly two differences: pivot_table is a generalization of pivot that can handle duplicate values for one pivoted index/column pair. Specifically, you can give pivot_table a list of aggregation functions using keyword argument aggfunc. The default aggfunc of pivot_table is numpy.mean. … Read more

Unpivot an Excel matrix/pivot-table?

Oh, well, it’s a little complicated. One of the problems is, the wizard-callup shortcuts don’t work in non-english versions of excels (damn, at home I would have the English version, but here at work…) Here’s a good video: https://www.youtube.com/watch?v=pUXJLzqlEPk But youtube videos can be deleted, so to make it a solid SO answer: First, you … Read more

Transpose rows into columns in MySQL

You can turn rows into a column with GROUP_CONCAT, but you can’t transpose whole result sets in any automatic way. You either write a query that produces each column manually, or you do it in an application. Here’s a tutorial on writing the complicated queries to emulate the transposition: http://www.artfulsoftware.com/infotree/queries.php#78

Reshaping/Pivoting data in Spark RDD and/or Spark DataFrames

Since Spark 1.6 you can use pivot function on GroupedData and provide aggregate expression. pivoted = (df .groupBy(“ID”, “Age”) .pivot( “Country”, [‘US’, ‘UK’, ‘CA’]) # Optional list of levels .sum(“Score”)) # alternatively you can use .agg(expr)) pivoted.show() ## +—+—+—+—+—+ ## | ID|Age| US| UK| CA| ## +—+—+—+—+—+ ## |X01| 41| 3| 1| 2| ## |X02| … Read more

Dynamically pivoting a table Oracle

This can be done dynamically the following way. First, here is the static version of the query so you can see the final sql: select c_id, p_id, max(case when r_key= ‘KEY1’ then r_value end) KEY1, max(case when r_key= ‘KEY2’ then r_value end) KEY2, max(case when r_key= ‘KEY3’ then r_value end) KEY3, max(case when r_key= ‘KEY4’ … Read more

Dynamic pivot query using PostgreSQL 9.3

SELECT * FROM crosstab ( ‘SELECT ProductNumber, ProductName, Salescountry, SalesQuantity FROM product ORDER BY 1’ , $$SELECT unnest(‘{US,UK,UAE1}’::varchar[])$$ ) AS ct ( “ProductNumber” varchar , “ProductName” varchar , “US” int , “UK” int , “UAE1” int); Detailed explanation: PostgreSQL Crosstab Query Pivot on Multiple Columns using Tablefunc Completely dynamic query for varying number of distinct … Read more