FAQ Database Discussion Community


Pivot SQL table (Rows into Columns)

sql,sql-server,table,pivot,pivot-table
I am currently returning data into a table in my SQL stored procedure. I am trying to pivot the rows into columns, and the columns into rows, but I am struggling to do so as a lot of the tutorials I am following to do this have laid out they're...

How to export data from a PivotTable to .csv in a specific format?

excel,excel-formula,pivot-table,export-to-csv,worksheet-function
I have a worksheet with 65000 rows. The first cell in each row is from a list of 1,500 values. For example: PartNumber1|Application1 PartNumber1|Application2 PartNumber1|Application3 PartNumber2|application1 Partnumber2|Application2 PartNumber3|Application1 PartNumber3|Application2 PartNumber3|Application3 Etc... I need to get an output like the following PartNumber1,Application1,application2,application3 PartNumber2,Application1,Application2 PartNumber3,Application1,Application2,Application3 ....etc Now, in Excel, I can create...

What is the syntax for pivot tables in Pandas? Docs don't seem to be right?

python,pandas,pivot-table
I might be totally crazy, but I'm reading the docs for pivot_table in Pandas, and even some guides Literally using the example from the docs with my own data: import pandas as pd df = pd.read_csv('data.csv') pivot_table(df, values='D', index=['A', 'B'], columns=['C'], aggfunc=np.sum) Produces an error: pivot_table(df, values='D', index=['A', 'B'], columns=['C'],...

Pivot in MySQL - Show first and last values depending on datetime-column

mysql,group-by,pivot-table,greatest-n-per-group,groupwise-maximum
after hours of researching and trying out myself I finally registered on Stackoverflow, which helped me for years by just reading. Thank you very much for this learning opportunity! I now hope to find help with a my MySQL-problem I can't get solved. I'm stuck. Source: I have a table...

Extract matching data from varying number of row

google-spreadsheet,spreadsheet,pivot-table
I am trying to build graphic indicators from a (potentially) large set of data using google-spreadsheet. So far, I've used a pivot table to extract the information from the raw data, and I want to build intermediate tables to calculate the different values I need for my indicators. So far,...

creating a calculated field in excel pivot table based on an item in a column

excel,pivot-table,calculated-field
Normally calculated pivot table fields in excel (2010) you click: design tab > options and sets > calculated field and if you had three columns: name, sales, cost name | sales | cost josh 10 2 your calculated field might be: profit which would be: = sales - cost. However...

How do I sum results of two if statements in the same cell?

excel,if-statement,excel-formula,excel-2010,pivot-table
I'm looking to have the results of two If statements calculated and added in the same cell. I'm getting #VALUE! error. =IF(ISERROR(GETPIVOTDATA("Sum of CHARGES",'Ship City'!$A$3,"ship_city",$B$7,"carrier_type",$A$27,"INV_month_id",D$6,"INV_year_id",$D$5,"Company Name",$B29)),"",GETPIVOTDATA("Sum of CHARGES",'Ship City'!$A$3,"ship_city",$B$7,"carrier_type",$A$27,"INV_month_id",D$6,"INV_year_id",$D$5,"Company Name",$B29))+IF(ISERROR(GETPIVOTDATA("Sum of CHARGES",'Recipient...

Excel pie charts from pivot table columns

excel,charts,pivot-table
I have a pivot table from a database that looks something like this: Person A Person B Person C Task A 1 3 Task B 1 2 Task C 2 1.5 1 The number is time spent on a task. If it's blank, the person didn't work on that task....

Display pivot table filter values

vba,excel-vba,excel-2010,pivot-table
I have a pivot table where I have applied a date filter: I am looking for a way to display the filter information in a cell. e.g. between 1/1/2015 and 10/3/2015 I have tried the following to just get it to display the information in a message box: Sub DisplayRange()...

How to line up columns of a pivot table

sql,sql-server,database,pivot,pivot-table
I have a pivot table that is being created like this: date Services Uptime Services Downtime Centers Downtime Centers Uptime ----- --------- - ------------------ ---------------- --------------- 12/5/14 100.00% 0.00% 100.00% 100.00% 12/12/14 100.00% 0.00% 0.00% 0.00% 12/19/14 100.00% 0.00% 100.00% 0.00% 12/26/14 100.00% 0.00% 100.00% 0.00% I would like it...

How to use PIVOT and JOIN together in SQL Server?

sql-server,join,sql-server-2012,pivot-table
This is table 'VendorItemPricing'. ItemID VendorName VendorPrice 122 HP 215.13 122 IBM 264.41 122 Microsoft 257.65 I used this query to get make rows as columns. Select ItemID, [HP] As HPPrice , [Apple] As ApplePrice, [Microsoft] As MicrosoftPrice, [IBM] As IBMPrice from ( select ItemID,VendorName,VendorPrice from VendorItemPricing where ItemID =...

Pivot Tables or Group By for Pandas?

python,pandas,count,group-by,pivot-table
I have a hopefully straightforward question that has been giving me a lot of difficulty for the last 3 hours. It should be easy. Here's the challenge. I have a pandas dataframe: +--------------------------+ | Col 'X' Col 'Y' | +--------------------------+ | class 1 cat 1 | | class 2 cat...

Import 400 rows in 20k row sheet and manipulate before import

excel,vba,excel-vba,optimization,pivot-table
I have 2 sheets with only 2 columns: Old URL New URL One sheet (master) contains 20k rows of those two columns and I have another sheet (toImport) with about 400 rows of those same two columns. Since it's for a redirect file sent to apache and I do this...

R: output a pivot-like table with subtotals

html,r,pivot-table,rmarkdown,xtable
I'm trying to make a cross tabulation in R, and having its output resemble as much as possible what I'd get in an Excel pivot table. The objective is to replace a report made manually with Excel and Word with one automated with R Markdown; data wrangling and charts have...

SQL: cell entries to matrix

sql-server,pivot-table
I have a table with entries representing cells on a matrix with x, y cordinates and the value for that position like this: COL1 COL2 COL3 .... .... ...... y1 x1 value11 y1 x2 value12 y1 x3 value13 y2 x1 value21 y2 x3 value23 All columns are Integer values and...

How to select SQL Server row values as columns (Dynamic Pivot)

sql,sql-server,pivot,pivot-table
I have one table where we are collecting status data from different sensors. For reporting purposes I would need to group information for each sensor (SGUID) to one row. Each sensor is sending reader (RGUID) and value for reader (VAL). Source table: Goal result: I have heard about pivot's but...

Excel VBA Pivot Cache Type Mismatch Run-Time Error '13'

excel-vba,runtime-error,pivot-table
Thanks for any input on this. I'm trying to make a simple pivot table that is taking data from sheet "5 Month Trending May 15" and putting it onto my Pivot Table sheet called "Errors By Criticality - Pivot". When I try to set the pivot cache range with the...

SUM(column) / COUNT(column) not working in Excel PivotTable

excel,pivot-table
I have a PivotTable in Excel, and I'm trying to add a custom field with a custom calcuation, but it doesn't seem to work. As of right now, the PivotTable looks like this... Part Count of Ordered Sum of Ordered myCustomField ABC 4 3500 3500 The formula for myCustomField looks...

PowerPivot % Difference from previous month

excel,date,pivot-table,powerpivot
When trying to get the percent difference from one month to the next, January (or the first month in the dataset) will always show as blank. How would I go about making Excel look for the previous month of last year? I am using DateStream as my date table. Issues...

What is the difference between pivottable.refreshtable and pivottable.update?

excel,excel-vba,pivot-table
What is the difference between the methods pivottable.refreshtable and pivottable.update? I have been googleling and haven't figured out if pivottable.refreshtable is enough to update a pivottable or if pivottable.update also does something significant....

Can I use Laravel 5 functionality to save positions per company per user?

php,jquery,mysql,laravel-5,pivot-table
This is my first question so please excuse any protocol errors! I am also new to Laravel 5. I have watched the Fundamental video series (especially "syncing tags" and have that working for simple pivot tables) and have searched the forum and further online but am not actually even sure...

how to create Pivot query for this?

sql,sql-server,sql-server-2008,pivot-table,powerpivot
I need output like this. In this no limit of shape; for example : [RD],[PC],[EM],[SE],[PR],[CM] My data looks like this : POINTER_RANGE SHAPE_NAME PCS CTS VALUE 0.30-0.39 RD 6 1.826 3503.17 0.40-0.49 RD 3 1.238 2837.62 0.50-0.69 PR 1 0.504 776.16 0.50-0.69 RD 5 2.618 8639.58 0.70-0.89 PC 5 4.109...

VBA - test if a value is a valid selection for a PivotField

excel-vba,excel-2007,pivot-table
For a pivot table (pt1) on Sheet1, I use VBA to change the value of a filter field (filterfield) using the code below. Let's say values for field can be A, B or C Sheets("Sheet1").PivotTables("pt1").PivotFields("filterfield").CurrentPage = "A" Occasionally, and let's say randomonly for the purposes of this question, A, B...

How to Pivot with Daily Averages based on Times in Excel?

excel,osx,pivot-table
This sheet contains a call log where there are a variable amount of calls per day, with a variable amount of call durations. My goal is to create a chart that shows the number of calls as each day passes, but also show the average call duration per day. I...

Pivot Table with multiple rows all having the same level hierarchy

excel,reporting,pivot-table,powerpivot
I have imported a bunch of data using PowerQuery into a single table and am building dashboard reporting. I have been using Pivot Tables to build my reports, which has worked fine so far. However, I've come to a point though where I want to simply show the count of...

Excel PivotTable; how to show values horizontally

excel,pivot-table
I'm trying to rearrange a pivot table that organizes all values (not sum or other statistic) from an original table. Seems simple but I can't find a way to make it values rather than sums. My original data looks like: Rank Name 1 A 1 B 2 C 2 D...

Sql Server Cross Tab query

sql-server,sql-server-2008-r2,pivot-table,crosstab
i have a data that contain date/time and other field like this ASSIGN ASSIGN_DATE OFFICER ASSIGN-1 2013-07-17 19:37:09.000 Admin ASSIGN-2 2013-07-17 19:37:09.000 Admin ASSIGN-3 2013-07-17 19:37:09.000 Admin ASSIGN-4 2013-07-17 10:40:15.000 TESTER ASSIGN-5 2013-07-17 19:37:09.000 Admin ASSIGN-5 2013-07-17 19:50:00.000 Admin ASSIGN-6 2013-07-17 10:40:15.000 TESTER ASSIGN-7 2013-07-17 10:40:15.000 TESTER i tried to...

QlikView : get data filtered in pivot table

pivot-table,qlikview
I found a great tutorial for building a nicer (dynamic) multibox, without extensions. I was able to use it, and I really love it. However, I have an issue: if I use a multibox with a master detail table, then if I filter it from detail then master will automatically...

Simple pandas pivot table issue

python,pandas,pivot-table
I have a dataframe like below: +-------+--------+----------+---------------+ | | ios_id | video_id | feed_position | +-------+--------+----------+---------------+ | 11995 | user1 | 199 | 7 | | 11996 | user2 | 164 | 18 | | 11997 | user3 | 209 | 1 | | 11998 | user3 | 85 |...

Pivot with ID and Dates

sql-server,sql-server-2008,pivot-table
I have this data set and Im trying to create a pivot table for the data below. I having some issues when trying to put in the date for each type across. Member ID Type Date 1 A 12/5/2014 1 b 3/6/2014 2 a 6/9/2015 2 b 3/2/2015 2 c...

VBA to change Pivot Filter in OLAP cube to a range

excel,vba,excel-vba,pivot-table,olap-cube
I have a pivot table that pulls data from an OLAP cube, and I'd like to create a macro to filter a pivot field that contains 'Week of Year' based on the value in another cell, so that I can easily change the time frame of the table. My experience...

Rearrange and regroup stacked data

excel,excel-formula,pivot-table
I have Excel data as follows: Mon 34 Mon 76 Mon 86 Tue 24 Tue 34 Tue 66 Wed 88 Wed 89 Wed 87 Is there a way with a formula to rewrite this data as follows: Mon Tue Wed 34 24 88 76 66 89 86 66 87 ...

Pivoting a single field in a table

sql-server,pivot-table
I have a table of the following structure: Name Type Line UniqueID Key Value I need to select all the fields except UniqueID field and add a key 'UniqueID' and a Value field with the value of the UniqueID to the result set. For example if I have three records:...

How to construct a pivot table that contains values based on certain conditions

excel,vba,pivot-table,powerpivot
I want to construct a pivot table from a huge data-set. Now I want to construct the pivot table that consists of values based on certain specified conditions, for example, the entries with age>25 will only be populated in the pivot table. How to do that without manually filtering out...

How can I concatenate values in an Excel pivot, as opposed to summing or counting

excel,concatenation,pivot-table
Say I have the following data: Col1 Col2 ------------ Bob Green Bob Green Bob Green Chris Blue Chris Blue Chris Red Dave Blue Dave Green Dave Red A default Pivot Table in Excel would yield: Col1 Col2 ------------ Bob Green Chris Blue Red Dave Blue Green Red Is there any...

SQL Server table manipulation

sql-server,table,structure,pivot,pivot-table
I am really new here. Currently working on SQL Server 2012. I have table A of the following structure (records are a subset of the full data table): CREATE TABLE [dbo].[TABLE_A]( [ASSET ID] [float] NULL, [TASK ID] [float] NULL, [IN_YEAR ] [float] NULL, [IN_WEEK] [float] NULL, [FLAG] [nvarchar](1) NULL )...

PivotTable for questionnaire analysis

excel,pivot-table,unpivot
I have some data from a reply form that I need to compile. I have some questions that have values from 1-5 or No-Yes. Example data: Date Question1 Question2 Question3 2010-12-01 4 3 No 2010-12-01 5 5 No 2010-12-01 4 3 Yes 2010-12-01 4 4 Yes 2010-12-01 3 4 No...

Convert rows to columns in sql server?

sql-server,sql-server-2012,pivot-table
I'm looking for an efficient way to convert rows to columns in SQL server, I heard that PIVOT is what I'm searching for, and I'm new to pivot tables. This is my example: ItemID VendorName VendorPrice 122 HP 125.66 122 Apple 130.44 122 Microsoft 134.00 122 IBM 124.90 This is...

Multiple criteria conditional formatting in a PivotTable

excel,table,pivot-table,excel-2013,conditional-formatting
Can you apply conditional formatting comparing multiple criteria (e.g. two pivot table columns)? I have a PivotTable in Excel 2013 that compares variable percentage rates and their corresponding values. I want to apply conditional formatting to the PivotTable by highlighting each cell that is greater than the adjacent percentage rate...

Derive two hours time blocks from start and end time fields

ms-access,pivot-table
I have a table in Microsoft Access that has a list of volunteers for an event. The table contains fields such as name, phone number, etc. There are three additional fields; Assignment which describes what activity the volunteer will be assigned to. For example, parking, front gate, ticket Booth, etc....

Excel 2010 - filter pivot table by pattern

excel,excel-2010,ssas,pivot-table
in Excel 2010 I am trying to analyze some data from an external analysis service. In a pivot table I am trying to filter the report by one field which has multiple values separated by a comma. These look like this: AB, CD1, EF1-5 AB, CD1,3, EF1 BCD, EFG EXG,...

Pivot Table to Reorganize Data

excel,excel-formula,pivot-table
I have data in the below format However, I need to convert it into the below format using pivot tables or other formuals such as where 2015 row is just split by months (divided by 12). Kindly help me with a logic that I can use. Thanks in advance for...

How to count multiple values in one single cell using Pivot Tables in Excel?

excel,excel-formula,pivot-table
I have this table in a Excel sheet: I would like to create a Pivot Table to have this outcome: Do you guys have any idea how to do it? Thank you!...

Change column names in Python Pandas from datatime objects to strings?

python,pandas,time-series,dataframes,pivot-table
Following this recipe. I 'pivoted' a dataframe that looks like this: Close 2015-02-20 14:00:00 1200.1 2015-02-20 14:10:00 1199.8 2015-02-21 14:00:00 1199.3 2015-02-21 14:10:00 1199.0 2015-02-22 14:00:00 1198.4 2015-02-22 14:10:00 1199.7 And turned it into this: 14:00 14:10 2015-02-20 1200.1 1199.8 2015-02-21 1199.3 1199.0 2015-02-22 1198.4 1199.7 However, now that I...

Get all records from Model that do NOT have an entry in pivot table Laravel 5

php,mysql,laravel,pivot-table,laravel-5
I am trying to figure out how to achieve the following. I have searched and searched to no avail. I have a pivot table in a Laravel 5 app that is working as expected with the following functions in the respective models. // Module.php //... public function sites() { return...

Laravel - returning only certain columns within a has function

php,laravel,laravel-4,eloquent,pivot-table
Currently, the function below works correctly, however, the ->has('alerts') returns the entire array of alerts including all relationship data. I want to return only certain columns from that relationship. public function getMatches() { $matches = Criteria::select('id') ->has('alerts') ->with(['alerts.location' => function($w){ $w->select('id'); }]) ->with('alerts.user.companies') ->where('user_id', Auth::id()) ->get(); return Response::json(array( 'matches' =>...

Cross Tab Query in MySQL

mysql,sql,pivot-table,crosstab
I created a sql select query, but I cannot get the records to display correctly. Table: gradeid | usrname | reviewmonth | program | total_score | pae -------------------------------------------------------------- 151 | smithj | 2 | math | 100% | 100% 152 | smithj | 2 | math | 95% | 100%...

PostgreSQL 9.3: Pivot table query

sql,postgresql,pivot-table,postgresql-9.3,table-functions
I want to show the pivot table(crosstab) for the given below table. Table: Employee CREATE TABLE Employee ( Employee_Number varchar(10), Employee_Role varchar(50), Group_Name varchar(10) ); Insertion: INSERT INTO Employee VALUES('EMP101','C# Developer','Group_1'), ('EMP102','ASP Developer','Group_1'), ('EMP103','SQL Developer','Group_2'), ('EMP104','PLSQL Developer','Group_2'), ('EMP101','Java Developer',''), ('EMP102','Web Developer',''); Now I want to show the pivot table for...

Excel: Pivot Table - Percentage of one value from another

excel,pivot-table,calculated-field
My pivot table has 3 columns: Row label (expand/collapse items in each row) Count of Case Count of Assigned My DATA sheet has 2 sets of data: "Case" (line item for every bug, feature, etc.) "Assigned" (name of the person assigned to the case) On the pivot table, how do...

Add a column description to a new (not Classic) PivotTable

excel,pivot,pivot-table
I want to add a column description to a PivotTable. Is it possible with "new" (not old "Classic") PivotTable? Here is source table example and desired results: I can achieve the results with "Classic" settings but this has some feature I do not like so I would prefer to use...

Get row count for all tables across dbs and pivot sql server 2012

sql,sql-server,sql-server-2012,pivot-table
I am using the following script to get a row count for all databases These databases are all identical in structure. USE [MyTestDb1]; SELECT 'MyTestDb1'as [Database Name], so.name AS [Table Name], rows AS [RowCount] FROM sysindexes AS si JOIN sysobjects AS so on si.id = so.id WHERE indid IN (0,1)...

What does the SSAS setting None in AggregateFunction do?

excel,ssas,pivot-table,cube
As I understand Microsoft's explanation setting the AggregateFunction of a measure to "None" should null out the value unless you are viewing the data at the lowest possible level. (i.e. the data element is a copy of the value in one exact cell in the cube). I've created a fact...

Sort entire Pivot table in Excel 2013 by column

sorting,excel-2010,pivot-table,excel-2013,powerpivot
I have a Pivot Table with many columns. I want the Pivot the ability to sort one of the columns in a way that the whole column is sorted and not the relative position in the hierarchy. Example: NAME PRODUCT SUM Joe A 400 Joe B 200 Joe B 300...

How to have multiple columns on Pivot tables?

sql-server,join,sql-server-2012,pivot-table,common-table-expression
I have a table in my sql server called, VendorItemPricing. select * from VendorItemPricing where ItemID = 122 And this query will give me the below results. ItemID VendorName VendorPrice UpdatedDate ObsoleteItem 122 HP 215.13 2015-05-15 11:55:49.983 0 122 IBM 264.41 2015-05-15 11:56:04.990 0 122 Microsoft 257.65 2015-05-15 11:56:23.963 0...

Mysql sales table grouped by customer and displayed by month columns pivot query

mysql,database,pivot-table
I have a sales table with the following columns: | Customer_Id | amount | date | What would be the best way to group data by customer_id, and displaying monthly total(SUM) of amount for each Customer_id (one row per Customer_id), on distinct month columns? The desired output would be something...

Normalized MySQL Pivot / Rows to Columns without unique names

php,mysql,sql,pivot-table
I've seem to hit a wall trying to figure out an appropriate query for the following database (simplified): 'Users' Table userID userName 1 John 2 Jane 'Ingredients' Table NOTE: This table will get fairly large! ingredientID ingredientName ingredientImage 1 Tomatoes tomato.png 2 Onions onion.png 3 Red Pepper redPepper.png 'Recipes' Table...

Pivot Table Using Linq

c#,linq,group-by,pivot,pivot-table
I have a table like this .(VisitType is dynamic) PersonelId VisitDate VisitTypeId 1 2015-02-24 A 2 2015-02-23 S 2 2015-02-24 D 4 2015-02-22 S 2 2015-02-22 A 2 2015-02-22 B 3 2015-02-23 A 1 2015-02-23 A 1 2015-02-24 D 4 2015-02-24 S 4 2015-02-22 S 2 2015-02-22 S 3 2015-02-24...

(kdb+/q) Pivot table: date in rows, symbols in columns, last price as values

pivot-table,kdb,q-lang
General pivot function provided at http://code.kx.com/wiki/Pivot goes: piv:{[t;k;p;v;f;g] v:(),v; G:group flip k!(t:.Q.v t)k; F:group flip p!t p; count[k]!g[k;P;C]xcols 0!key[G]!flip(C:f[v]P:flip value flip key F)!raze {[i;j;k;x;y] a:count[x]#x 0N; a[y]:x y; b:count[x]#0b; b[y]:1b; c:a i; c[k]:first'[a[j]@'where'[b j]]; c}[I[;0];I J;J:where 1<>count'[I:value G]]/:\:[t v;value F]} given that f and g are defined as f:{[v;P]`$raze each...

Making a sorted list [closed]

python,r,excel,dictionary,pivot-table
I have been given a set of 20,000 entries in Excel. Each entry is a string, and they are all names of events such as: Daytona 500, NASCAR, 3x1 Brand Rep, etc. Many of the event names are repeated, and I would like to make a list and sort them...

Laravel 4 - Scope to only show rows where another DB has no relational items

laravel-4,eloquent,pivot-table
I have a cars table, and a car_image table. I want to get all the cars that have NO images in the car_image. In my car model I am making a scope: public function scopeNoImages($query) { return $query-> ?? } How can I create a scope that will show only...

Sum Colum case () after pivot

sql-server,sql-server-2005,pivot-table
Sorry if I don´t explain very well, and the title isn´t very clear. I´m using SQL Server 2005. I have a query with Pivot that is working fine, but know I must add a new query that get resuls from quarterly. This is my query to get result from Month...

How to both pivot a table and assign event aligned IDs

r,pivot-table
I need to accomplish 2 task that might be best handled at the same time. I need to pivot a simple table but while pivoting I need to normalize one of the columns like almost like a rank value. Say we start with a table of unit sales by date...