pivot-table

melt / reshape in excel using VBA?

I’ve got two posts, with usable code and downloadable workbook, on doing this in Excel/VBA on my blog: http://yoursumbuddy.com/data-normalizer http://yoursumbuddy.com/data-normalizer-the-sql/ Here’s the code: ‘Arguments ‘List: The range to be normalized. ‘RepeatingColsCount: The number of columns, starting with the leftmost, ‘ whose headings remain the same. ‘NormalizedColHeader: The column header for the rolled-up category. ‘DataColHeader: The … Read more

Dynamic Pivot Columns in SQL Server

Something like this: DECLARE @cols AS NVARCHAR(MAX); DECLARE @query AS NVARCHAR(MAX); select @cols = STUFF((SELECT distinct ‘,’ + QUOTENAME(Name) FROM property FOR XML PATH(”), TYPE ).value(‘.’, ‘NVARCHAR(MAX)’) , 1, 1, ”); SELECT @query = ‘SELECT * FROM ( SELECT o.object_id, p.Name, o.value FROM propertyObjects AS o INNER JOIN property AS p ON o.Property_Id = p.Id … Read more

Pandas Pivot tables row subtotals

If you put State and City not both in the rows, you’ll get separate margins. Reshape and you get the table you’re after: In [10]: table = pivot_table(df, values=[‘SalesToday’, ‘SalesMTD’,’SalesYTD’],\ rows=[‘State’], cols=[‘City’], aggfunc=np.sum, margins=True) In [11]: table.stack(‘City’) Out[11]: SalesMTD SalesToday SalesYTD State City stA All 900 50 2100 ctA 400 20 1000 ctB 500 30 … Read more

mysql select dynamic row values as column names, another column as value

Unlike some other RDBMS MySQL doesn’t have native support for pivoting operations of this sort by design (the developers feel it’s more suited to the presentation, rather than database, layer of your application). If you absolutely must perfom such manipulations within MySQL, building a prepared statement is the way to go—although rather than messing around … Read more

Pivot data using LINQ

I’m not saying it is a great way to pivot – but it is a pivot… // sample data var data = new[] { new { Foo = 1, Bar = “Don Smith”}, new { Foo = 1, Bar = “Mike Jones”}, new { Foo = 1, Bar = “James Ray”}, new { Foo = … Read more

How do you create a “reverse pivot” in Google Sheets?

I wrote a simple general custom function, which is 100% reusable you can unpivot / reverse pivot a table of any size. In your case you could use it like this: =unpivot(A1:D4,1,1,”customer”,”sales”) So you can use it just like any built-in array function in spreadsheet. Please see here 2 examples: https://docs.google.com/spreadsheets/d/12TBoX2UI_Yu2MA2ZN3p9f-cZsySE4et1slwpgjZbSzw/edit#gid=422214765 The following is the … Read more

Is it possible to Pivot data using LINQ?

Something like this? List<CustData> myList = GetCustData(); var query = myList .GroupBy(c => c.CustId) .Select(g => new { CustId = g.Key, Jan = g.Where(c => c.OrderDate.Month == 1).Sum(c => c.Qty), Feb = g.Where(c => c.OrderDate.Month == 2).Sum(c => c.Qty), March = g.Where(c => c.OrderDate.Month == 3).Sum(c => c.Qty) }); GroupBy in Linq does not work … Read more

Convert matrix to 3-column table (‘reverse pivot’, ‘unpivot’, ‘flatten’, ‘normalize’)

To “reverse pivot”, “unpivot” or “flatten”: For Excel 2003: Activate any cell in your summary table and choose Data – PivotTable and PivotChart Report: For later versions access the Wizard with Alt+D, P. For Excel for Mac 2011, it’s ⌘+Alt+P (See here). Select Multiple consolidation ranges and click Next. In “Step 2a of 3”, choose … Read more