SQL Server Dynamic Pivot Column Names
DECLARE @cols NVARCHAR (MAX) SELECT @cols = COALESCE (@cols + ',[' + COLUMN_NAME + ']', '[' + COLUMN_NAME + ']') FROM (SELECT DISTINCT COLUMN_NAME,INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION O FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CODES') PV ORDER BY O DECLARE @query NVARCHAR(MAX) SET @query = ' SELECT TOP 0 * FROM ( SELECT COLUMN_NAME...
Pandas: pivot_table unique count returns count
python,pandas,unique,pivot-table
Use a groupby to get at each combination of col_1 and col_3, then unstack to get the col_3 values as columns: # Copying your data and reading from the clipboard: df = pd.read_clipboard() unique_counts = df.groupby(['col_1', 'col_3'])['col_2'].unique().map(len) unstacked = unique_counts.unstack(level='col_3').fillna(0) Output: unstacked Out[18]: col_3 type_1 type_3 type_4 col_1 location_1 1...
Cross Tab Query in MySQL
mysql,sql,pivot-table,crosstab
This query fulfills the horizontal demand of your question. I am not concerned what you are trying to do. Please make sure if this works for you. SELECT CASE WHEN program = 'Math' AND reviewmonth = 1 THEN ROUND(AVG( IF(pae = 79, (IF(pae < total_score, pae,total_score)),total_score)),2) ELSE 'NULL' END AS...
What does the SSAS setting None in AggregateFunction do?
When Aggregate Function of a Measure is set to 'None', SSAS does not calculate the measure for the 'All' member in any dimension. Put in other words, we will have only leaf-level values for measures and that too only for dimensions that are directly linked to that fact. You can...
Laravel - Can't access pivot table data
sqlite,laravel,laravel-4,eloquent,pivot-table
The problem happens to be that some Items don't have a Length assigned. So $item->length->first() is null and therefore accessing pivot raises the error. The solution for this is fairly simple. Just wrap an if around it and you should be fine. <tr> @if($length = $item->length->first()) <td>{{ $length->pivot->value }}</td> @endif...
Import 400 rows in 20k row sheet and manipulate before import
excel,vba,excel-vba,optimization,pivot-table
Assuming that your Old URL and New URL are in columns A and B of the two sheets, you could do the following procedure: Sort master and toImport, each by column A. Add the following formula to column C of master: = IF(ISNA(VLOOKUP(A1, toImport!A:A, 1, FALSE)), IF(ISNA(VLOOKUP(B1, toImport!A:A, 1, FALSE)),...
How to count multiple values in one single cell using Pivot Tables in Excel?
excel,excel-formula,pivot-table
If you're okay with using helper columns, use text to columns to split up your third column into individual columns by using commas as the delimiter. Get everything in one column and then create your pivot table on the resulting data. EDIT: Select C2:C5 and go to the data tab...
Excel: Pivot Table - Percentage of one value from another
excel,pivot-table,calculated-field
Looks like you cannot use count() in the calculated fields. I would try to manipulate the source data so that you are summing numbers and not counting 'names'. Then, create a calculated field as shown below: ...
Display pivot table filter values
vba,excel-vba,excel-2010,pivot-table
I think you need VBA for this. By running the Macro Recorder while adding a date filter I came up with: Sub GetPivotFilterDates() Dim pvt As Excel.PivotTable Dim pvtField As Excel.PivotField Set pvt = Worksheets(1).PivotTables(1) Set pvtField = pvt.PivotFields("Date Range") With pvtField.PivotFilters(1) If .FilterType = xlDateBetween Then Worksheets(1).Range("A1").Value = "Filter...
Pivot table - deselect less than a value
Try this On Error Resume Next For i = 10 To 0 Step -1 .PivotItems(i).Visible = False Next i On Error GoTo 0 ...
Using Pivot on SQL
sql,sql-server,pivot,pivot-table
In your inner query you should select only the columns you really need. Just comment the following columns in your inner query: Co_EnfermedadPatologica Fl_Diagnostico T_AntecedentePersonal.No_Diagnostico Hope this helps....
Excel 2010 - filter pivot table by pattern
excel,excel-2010,ssas,pivot-table
Go to the drop down filter that you have in the pivot table, click on Select Multiple Items and deselect ALL. Then in the search bar (again at the pivot table filter) write *e* and select add current selection to filter. Repeat for *h*. Do not forget to select add...
Get row count for all tables across dbs and pivot sql server 2012
sql,sql-server,sql-server-2012,pivot-table
Yes. You need PIVOT at this point. First of all declare a variable to get column names dynamically DECLARE @cols NVARCHAR (MAX) SELECT @cols = COALESCE (@cols + ',[' + [Database Name] + ']', '[' + [Database Name] + ']') FROM ( SELECT DISTINCT 'MyTestDb1'as [Database Name] FROM sysindexes AS...
How to both pivot a table and assign event aligned IDs
Try df$indx <- with(df, ave(seq_along(Event), Event, FUN=seq_along)) library(reshape2) dcast(df, indx~Event, value.var='Units') # indx A B C #1 1 5 10 15 #2 2 10 8 18 #3 3 12 NA NA Or xtabs(Units~indx+Event, df) ...
How to write the sql to select the sum from three tables and group by date
mysql,sql,select,group-by,pivot-table
Try this: SELECT a.id, a.name, SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM w.date) = 201401 THEN 1 WHEN EXTRACT(YEAR_MONTH FROM s.date) = 201401 THEN 1 ELSE 0 END) AS Jan14, SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM w.date) = 201402 THEN 1 WHEN EXTRACT(YEAR_MONTH FROM s.date) = 201402 THEN 1 ELSE 0 END) AS Feb14, SUM(CASE...
Pivot with ID and Dates
sql-server,sql-server-2008,pivot-table
Assuming each member will only have one value for each type you could use conditional aggregation to get the result you want: select memberid, max(case when type='a' then 'Yes' end) as "a", max(case when type='a' then date end) as "a date", max(case when type='b' then 'Yes' end) as "b", max(case...
How to construct a pivot table that contains values based on certain conditions
excel,vba,pivot-table,powerpivot
What you are trying to achieve has no clean solution, I can think of two dirty ones. Modify the source data/clone the source data and modify that Modify the SourceData property of the pivot table by code Option 1 is easier, safer, but not good if you are working with...
Sql Server Cross Tab query
sql-server,sql-server-2008-r2,pivot-table,crosstab
Maybe it's not the most efficient solution, but it works just fine: Test data: CREATE TABLE #Test ( ASSIGN VARCHAR(255) , ASSIGN_DATE DATETIME2 , OFFICER VARCHAR(255) ); INSERT INTO #Test (ASSIGN, ASSIGN_DATE, OFFICER) VALUES ('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',...
Percentage of non-numeric answers in PivotTable?
excel,pivot-table,percentage,worksheet-function,countif
If your results are in Column A for example, then something like: ="Less than $1 million: "&(COUNTIF(A:A,"Less than $1 million")/COUNTA(A:A))*100&"%" should do you. If you have a header in Row 1 then change COUNTA(A:A) to COUNTA(A:A)-1 based on your example above, this formula would return "Less than $1 million: 62.5%"...
Sort entire Pivot table in Excel 2013 by column
sorting,excel-2010,pivot-table,excel-2013,powerpivot
If you have more than one field in the row area of the pivot table, you cannot create a sort purely by value. The hierarchy cannot be ignored. That's how a pivot table works. Don't shoot the messenger.
How to have multiple columns on Pivot tables?
sql-server,join,sql-server-2012,pivot-table,common-table-expression
You could use either a Subquery or CTE and then join all three queries based on ItemID to get all the neccessary fields: ;WITH q1 AS ( SELECT ItemID ,[HP] AS HPUpdatedDate ,[Apple] AS AppleUpdatedDate ,[Microsoft] AS MicrosoftUpdatedDate ,[IBM] AS IBMUpdatedDate FROM ( SELECT Itemid ,Vendorname ,UpdatedDate FROM VendorItemPricing WHERE...
What is the difference between pivottable.refreshtable and pivottable.update?
Update merely updates the layout - it doesn't refresh the data.
R: output a pivot-like table with subtotals
html,r,pivot-table,rmarkdown,xtable
Replace the left hand side with: ministry * (department + 1) + 1 That is, try this: tabular(ministry * (department + 1) + 1 ~ ((Count = budget) + (Avg = (mean * budget)) + (Total = (sum * budget))), data = df) giving: Avg Total ministry department Count budget...
Laravel - retrieve direct/indirect pivot table subscribers
This will be the easiest way: // assuming this setup: User hasMany Question User belongsToMany User (subscribers - direct) Question belongsToMany User (subscribers - indirect) // we don't need this, since we pass it by reference to the closure, // but for clarity let's initialize this variable: $indirectSubscribers = null;...
How to find groups of individuals who attended multiple events together?
excel,vba,excel-vba,pivot-table
I take it this is an exercise and you are not interested in an alternative approach, but should someone else stumble across this post in earnest the easiest solution may be to apply a PivotTable: ...
Pivot Table Using Linq
c#,linq,group-by,pivot,pivot-table
Try this: //static headers version var qry = Visits.GroupBy(v=>new{v.VisitDate, v.PersonelId}) .Select(g=>new{ VisitDate = g.Key.VisitDate, PersonelId = g.Key.PersonelId, A = g.Where(d=>d.VisitTypeId=="A").Count(), B = g.Where(d=>d.VisitTypeId=="B").Count(), D = g.Where(d=>d.VisitTypeId=="D").Count(), S = g.Where(d=>d.VisitTypeId=="S").Count() }); //dynamic headers version var qry = Visits.GroupBy(v=>new{v.VisitDate, v.PersonelId}) .Select(g=>new{ VisitDate = g.Key.VisitDate, PersonelId = g.Key.PersonelId, subject =...
PowerPivot % Difference from previous month
excel,date,pivot-table,powerpivot
You would need to add a calculated field to calculate the total for the same period last year...i.e. TotalSPLY:=CALCULATE(SUM( 'TableName'[Total]), SAMEPERIODLASTYEAR( DateTable[DateKey])) https://support.office.com/en-sg/article/SAMEPERIODLASTYEAR-Function-DAX-b8f7f423-22f5-470f-abd3-b76a1250bcc1?ui=en-US&rs=en-SG&ad=SG UPDATE -- (to adhere to the previous month from last year requirement) =CALCULATE(SUM( 'TableName'[Total]), DATESBETWEEN( 'DateTable'[DateKey], IF( DATE( YEAR( EOMONTH( MIN( 'DateTable'[DateKey]),-13) ), MONTH(...
Excel VBA Pivot Cache Type Mismatch Run-Time Error '13'
excel-vba,runtime-error,pivot-table
The documentation for PivotCaches.Create indicates The SourceData argument is required if SourceType isn't xlExternal. It can be a Range object (when SourceType is either xlConsolidation or xlDatabase) or an Excel Workbook Connection object (when SourceType is xlExternal). Despite this, the macro recorder will always create a String here for the...
Extract matching data from varying number of row
google-spreadsheet,spreadsheet,pivot-table
I think this is possible using a formula: ={QUERY(A2:D,"select A,count(D) where A <> '' group by A pivot C"),QUERY(A2:D,"select count(D) where A <> ''group by A pivot B")} Here is an example sheet (the formula is in cell F1): https://goo.gl/T9xI1v...
SQL: cell entries to matrix
SAMPLE TABLE CREATE TABLE #TEMP(COL1 VARCHAR(50),COL2 VARCHAR(50),COL3 VARCHAR(50)) INSERT INTO #TEMP SELECT 'y1', 'x1', 'value11' UNION ALL SELECT 'y1', 'x2', 'value12' UNION ALL SELECT 'y1', 'x3', 'value13' UNION ALL SELECT 'y2', 'x1', 'value21' UNION ALL SELECT 'y2', 'x3', 'value23' Get the columns for pivot DECLARE @cols NVARCHAR (MAX) SELECT @cols...
Pivot Tables or Group By for Pandas?
python,pandas,count,group-by,pivot-table
You could use pd.crosstab() In [27]: df Out[27]: Col X Col Y 0 class 1 cat 1 1 class 2 cat 1 2 class 3 cat 2 3 class 2 cat 3 In [28]: pd.crosstab(df['Col X'], df['Col Y']) Out[28]: Col Y cat 1 cat 2 cat 3 Col X class...
Sharepoint 2010 Excel Web Part displaying Pivot Table colors incorrectly
excel,sharepoint-2010,web-parts,pivot-table
We were able to resolve the problem by restarting the Excel Services component of Sharepoint 2010.
how can I print multiple rows data in one column?
mysql,sql,select,group-by,pivot-table
Try this: SELECT t.date, MAX(CASE WHEN t.timeslot = '10 - 12' THEN s.name ELSE '' END) AS `10 - 12`, MAX(CASE WHEN t.timeslot = '2 - 4' THEN s.name ELSE '' END) AS `2 - 4`, t.venue FROM TimeSchedule t INNER JOIN `subject` s ON t.subID = s.subID GROUP BY...
How to export data from a PivotTable to .csv in a specific format?
excel,excel-formula,pivot-table,export-to-csv,worksheet-function
My hunch is PivotTables are irrelevant here (and that this is more "one off" than "routine") so suggest: Work on a copy. Parse data assumed to be in ColumnA with Text to Columns and pipe as the delimiter. Insert a row at the top. In C2: =IF(A1=A2,C1&","&B2,A2&","&B2) in D2: =A2<>A3...
Drag all measure values from SSAS cube pivot table
It depends what tool you are using to view the cube. In SQL Server Management Studio, you are able to drag the entire list of measures across in one go (but not individual measure groups) In Excel, however you are limited to single measures at a time. You can "Defer...
SQL Query conversion from Access to MySQL
javascript,php,mysql,sql-server,pivot-table
There is no TRANSFORM in MySQL. TRANSFORM is really just an alias so MS Access will do the work for you in creating "cross-tab" queries and you can use less code. Instead use the ANSI SQL way. http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=25 Example of ANSI SQL cross tab query: Example ANSI-SQL Crosstab SELECT SUM(CASE...
PivotTable for questionnaire analysis
Unpivot (eg as described here), sort the Table on Column to move Question3 to the bottom. Convert to Range and pivot data down to the end of Question2 with Row above Column for ROWS, Value for COLUMNS and Count of Value for VALUES. Add a row above the PT and...
SUM(column) / COUNT(column) not working in Excel PivotTable
Instead of using a calculated field like this, put another Value in your pivot table and make it the Average of Ordered.
VBA CalculatedFields Pivot USe Variables as Field References?
excel,vba,excel-vba,pivot-table
currMonth is initialized as: currMonth = "Jan-14" but should be: currMonth = "='Jan-14'" Do you see the difference?...
How do I sum results of two if statements in the same cell?
excel,if-statement,excel-formula,excel-2010,pivot-table
Your both IF functions return an empty string "". Using + operator with strings returns #VALUE!. There are different methods to fix it: use 0 instead of "" use SUM instead of + (it ignores strings). And as @John Bustos mentioned in his comment, you can simplify your formula with...
laravel syncing many-to-many relationship
php,laravel,relational-database,eloquent,pivot-table
Yes you're array has the wrong structure. If you look at the docs (need to scroll a bit down) the id should be the key of the array. Like so: array( '1' => array('is_admin' => 1), '85' => array('is_admin' => 0), '86' => array('is_admin' => 0), ); You can do...
SQL - Pivot table from a multilayer SELECT statement
Irrespective of the complexity of your query, you can wrap it and then PIVOT it as follows, provided that the columns being pivoted match those returned from the inner select. I've assumed a SUM aggregation on TAKSIT SELECT * FROM ( SELECT * FROM YourReallyComplexQueryHere ) x PIVOT ( SUM(TAKSIT)...
Laravel 4 - Scope to only show rows where another DB has no relational items
laravel-4,eloquent,pivot-table
Assuming the car_image consists of the images of the cars: In your Car model: public function images() { return $this->hasMany('CarImage'); } In your CarImage model: public function car() { return $this->belongsTo('Car'); } Now you can load the cars without images like so: return Car::doesntHave('images')->get(); Using: doesntHave....
QlikView : get data filtered in pivot table
The GetFieldSelections() will only give you the list of explicitly selected items. By selecting items in the detail, you're only selecting from the master implicitly. Try using Concat() instead. This will give you the list of possible values for a field that isn't being directly selected upon. Your next big...
pivot transform for multiple weeks
sql,tsql,sql-server-2008-r2,pivot,pivot-table
You should use a calendar table to get the first and last day of the week. Select all relevant fields in a cte and then pivot from there: ;WITH cte AS (SELECT ConfirmationId , DayName , WorkingHours , b.FirstDateOfWeek , b.LastDateOfWeek FROM #temp1 a JOIN tlkp_Calendar b ON a.TimeSheetDate =...
Condensing Interval Time in Excel Pivot Table
Just like @pnuts said, but here's a full-blown example. ...
Pivot Table with multiple rows all having the same level hierarchy
excel,reporting,pivot-table,powerpivot
If I understand you correctly, here's an example that shows what you're trying to achieve: The table on the left has the TRUE/FALSE entries and the PivotTable on the right just shows the number of true items in each of those columns. The format of the DAX measure to produce...
Making a sorted list [closed]
python,r,excel,dictionary,pivot-table
In Excel I would use a PivotTable, about 15 seconds to set up: ...
Laravel Removing Pivot data in many to many relationship
You have asked and you shall receive your answer. But first a few words to sum up the comment section. I personally don't know why you would want / need to do this. I understand if you want to hide it from the output but not selecting it from the...
SQL Server - trying to format a table, maybe with PIVOT [duplicate]
Try this using FOR XML PATH select Email, Username = stuff(( select ', ' + t2.Username from #table1 t2 where t2.Email = t1.Email group by t2.Username for xml path(''), type).value('.', 'varchar(max)' ), 1, 2, '') from #table1 t1 ...
Sum Colum case () after pivot
sql-server,sql-server-2005,pivot-table
Try something like..... ; WITH PivotData AS ( SELECT idWatimetro ,CASE WHEN mes IN (1,2,3) THEN 1 WHEN mes IN (4,5,6) THEN 2 WHEN mes IN (7,8,9) THEN 3 WHEN mes IN (10,11,12) THEN 4 END AS mes ,ano ,valor FROM E_Registros_Watimetros where ano = 2012 ) SELECT * FROM...
Laravel - returning only certain columns within a has function
php,laravel,laravel-4,eloquent,pivot-table
You don't want to use has or whereHas for that. The has functions are only to filter the result based on a relation. To only select certain columns use with() $matches = Criteria::select('id') ->has('alerts') ->with(['alerts' => function($q){ $q->select('id', 'pivot.criteria_id'); }, 'alerts.location' => function($w){ $w->select('id'); }]) ->with('alerts.user.companies') ->where('user_id', Auth::id()) ->get(); ...
Change column names in Python Pandas from datatime objects to strings?
python,pandas,time-series,dataframes,pivot-table
You can convert the column names to strings like this: df.columns =df.columns.map(lambda t: t.strftime('%H:%M')) or using rename: df.rename(columns =lambda t: t.strftime('%H:%M'), inplace=True) and then index them: df['14:00'] returns: 2015-02-20 2399.9 2015-02-21 NaN 2015-02-22 NaN Name: 14:00, dtype: float64 ...
VBA to change Pivot Filter in OLAP cube to a range
excel,vba,excel-vba,pivot-table,olap-cube
It should be something like this: Dim aWeeks() Dim n As Long Dim x As Long n = Range("A1").Value ReDim aWeeks(n - 1) For x = 1 To n aWeeks(x - 1) = "[Time].[Week of Year].&[" & x & "]" Next x ActiveSheet.PivotTables("PivotTable3").PivotFields( _ "[Time].[Week of Year].[Week of Year]").VisibleItemsList =...
Pivot SQL table (Rows into Columns)
sql,sql-server,table,pivot,pivot-table
This is done by first unpivoting and then pivoting. Here is an example, that you can adjust yo your data: DECLARE @t TABLE(col0 VARCHAR(20), col1 MONEY, col2 MONEY, col3 MONEY) INSERT INTO @t VALUES ('aaaaaa', 1, 1.2, 0), ('bbbbbb', 2, 2.2, 0), ('cccccc', 3, 3.3, 100), ('dddddd', 4, 4.4, 0)...
Excel pivot connection renamed to “Connection” in VBA
excel-vba,connection-string,pivot-table
I have also encountered this bug when updating the CommandText property of an ODBC connection. I found that if you temporarily switch to an OLEDB connection, update your CommandText property and then switch back to ODBC it does not create the new connection. Don't ask me why... this just works...
Rearrange and regroup stacked data
excel,excel-formula,pivot-table
Assuming 76 is in B2, insert a column on the left and a row above. Label the columns (say ID, day and value) and in A2 enter 1 and series fill down to A4. Then select A2:A4 and series fill down to suit. Build a PivotTable with ID for ROWS,...
Excel VBA Creating Pivot not fully working
excel,vba,excel-vba,pivot-table
Do it Like this: Dim PC As Excel.PivotCache Dim PT As Excel.PivotTable S1LastRow = 569 S1LastColumn = 17 Sheet2.UsedRange.Delete Application.Goto Sheet2.Range("A1"), True Set PC = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _ SourceData:="Main!R1C1:R" & S1LastRow & "C" & S1LastColumn, _ Version:=xlPivotTableVersion12) Set PT = PC.CreatePivotTable(TableDestination:="Graph!R1C1", TableName:="Summary", _ DefaultVersion:=xlPivotTableVersion12) With PT ' add the data field...
How to get array with rows related to another table for Bllim Datatable - Laravel
php,laravel-4,datatable,pivot-table,has-and-belongs-to-many
You need to use the next sentence: /* The client whose ID is 1*/ $client = Client::find(1); $client->books() ->getQuery() ->getQuery() ->select(array('id', 'ColumnA', 'ColumnB')); You can use the where clausule also: $client->books() ->getQuery() ->getQuery() ->select(array('id', 'ColumnA', 'ColumnB')) ->where('id','=',1); Note which I used getQuery() twice, this is because Bllim/Datatables need an Query/Builder...
Summing over particular columns of data frame in R
you could use aggregate > aggregate(population~continents, data=countries, FUN=sum) continents population 1 Afr 2000 2 Am 4500 3 Eur 4500 Take a look at this answer for further alternatives...
Can I use Laravel 5 functionality to save positions per company per user?
php,jquery,mysql,laravel-5,pivot-table
I asked another question - Laravel 5: synching an extra field via pivot (answered by @lukasgeiter) - and that helped me solve my issue so I would like to have this question marked as closed.
Derive two hours time blocks from start and end time fields
Okay, so given your sample data, a Pivot table is not ideal because the lack of a table/join table with times and who can work when, etc. So, I created a function that returns "o" or "x" depending on if someone's availability fits into the time slots you've provided us...
SQL Server table manipulation
sql-server,table,structure,pivot,pivot-table
Give this code a try for size and see how you get on. It first takes all the unique year/weeks and then creates a select list. Then using dynamic T-SQL it creates a pivot table to show your desired layout: SELECT DISTINCT CAST(IN_YEAR AS VARCHAR) + '-WEEK '+CAST(IN_WEEK AS VARCHAR)...
Flatten DataFrame with multi-index columns
>>> piv.unstack().reset_index().drop('level_0', axis=1) goods category month 0 0 a c1 1 5 1 a c1 2 5 2 a c1 3 5 3 a c2 1 0 4 a c2 2 10 5 a c2 3 10 6 b c1 1 30 7 b c1 2 30 8 b c1...
How can I iterate over Pandas pivot table? (A multi-index dataframe?)
You don't need to iterate the dataframe, Pandas has already provided a method to convert dataframe to sql by DataFrame.to_sql(...). Alternatively, if you want to manually insert data into database, you can use Pandas' to_csv(), for example: I have a df like this: df A B first second bar one...
Excel pie charts from pivot table columns
Generally, the Pivot Chart will attempt to plot whatever is in the Pivot Table (including all column titles). I think the easiest way to deal with this is to add a Slicer so that you can select the Person and have it filter the data for you. Alternatively, you can...
Pivoting denormalized rows--converting multiple rows to single row--causes slow LEFT JOIN
mysql,sql,join,left-join,pivot-table
Ah, the joys of denormalized data sets. It's not what you asked, but could you redo this FIM2 table like this, so there's just one item in each row rather than three? Your life would get easier if you did this. KPIN KSEQ KFLD ATTRIBUTE R0205 1 1 COLD ROLLED...
Simple pandas pivot table issue
You almost had it, just don't include 'video_id' in columns : columns is just for what's going along the top of the pivot table, and index is for what's going down the left. pd.pivot_table(df2, columns = ['feed_position'], index = ['video_id'], aggfunc= np.count_nonzero) ios_id feed_position 1 2 4 5 6 10...
How to Pivot with Daily Averages based on Times in Excel?
In the pivot table, put the date into the row labels box and the duration into the ∑ Values box. Click on the down arrow where it says Sum of Duration and select Value Field Settings. Change Sum to Average under Summarize Field By. Click the Number Format button and...
How to select SQL Server row values as columns (Dynamic Pivot)
sql,sql-server,pivot,pivot-table
You need a dynamic pivot using some dynamic T-SQL: CREATE TABLE #VALS ( VALS NVARCHAR(4000) ) INSERT INTO #VALS SELECT DISTINCT RGUID FROM [TABLE] DECLARE @SQL NVARCHAR(MAX) DECLARE @VALS NVARCHAR(4000) SELECT @VALS = COALESCE(@VALS+', ','') + '[' + VALS + ']' FROM #VALS SET @SQL = ' SELECT SGUID, '[email protected]+'...
VBA - test if a value is a valid selection for a PivotField
excel-vba,excel-2007,pivot-table
You can iterate through the PivotItems and check the Name against your test. Sub CheckIfPivotFieldContainsItem() Dim pt As PivotTable Set pt = Sheet1.PivotTables(1) Dim test_val As Variant test_val = "59" Dim pivot_item As PivotItem For Each pivot_item In pt.PivotFields("C").PivotItems If pivot_item.Name = test_val Then Debug.Print "MATCHES" End If Next pi...
Get all records from Model that do NOT have an entry in pivot table Laravel 5
php,mysql,laravel,pivot-table,laravel-5
Starting the query from the Module side and excluding with whereDoesntHave should work in this case: $id = 1; $modules = Module::whereDoesntHave('sites', function($q) use ($id){ $q->where('site_id', $id); })->get(); ...
Pivoting a single field in a table
This is kind of an odd request, at least to me. But anyhow, you can approach it a few ways. One way is to use cross apply: select name, type, line, newkey, newvalue from table1 cross apply ( values ('UniqueID',uniqueid), ('SSN',Value), ('Address',Value) ) c (newkey,newvalue) SQL Fiddle demo Based on...
Add a column description to a new (not Classic) PivotTable
Add Count of I2 manually with formatting and in PIVOTTABLE TOOLS > DESIGN > Layout , Report Layout select Show in Tabular Form and if necessary uncheck Subtotal "line".
Mysql sales table grouped by customer and displayed by month columns pivot query
Let's say you have following table: mysql> select * from sales; +-------------+--------+------------+ | customer_id | amount | date | +-------------+--------+------------+ | 1 | 12 | 2015-01-01 | | 1 | 1 | 2015-01-02 | | 1 | 663 | 2015-02-12 | | 2 | 22 | 2015-01-03 | | 2...
What is the syntax for pivot tables in Pandas? Docs don't seem to be right?
pivot_table is a top-level function, as such you need to qualify it using pd.pivot_table. The code in the docs assumes you've done the following: from pandas import * Which is where the confusion stems from. So from the example in the docs: In [41]: t="""A B C D 0 foo...
Creating Multi dimension pivot table in R
Try library(dplyr) df %>% group_by(Scenario, Year) %>% summarise(Cost=sum(Cost)) Or library(data.table) setDT(df)[, list(Cost=sum(Cost)), by=list(Scenario, Year)] Or aggregate(Cost~Scenario+Year, df,sum) data df <- structure(list(Event = 1:12, Scenario = c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L), Year = c(1L, 1L, 2L, 2L, 1L, 1L, 2L, 2L, 1L, 1L, 2L,...
Convert rows to columns in sql server?
sql-server,sql-server-2012,pivot-table
declare @table table (Itemid Int,Vendorname varchar(10),VendorPrice DECIMAL(18,2)) insert into @table (Itemid,Vendorname,VendorPrice) values (122,'HP',125.66), (122,'Apple',130.44), (122,'microsoft',134.00), (122,'IBM',124.90) Select Itemid,[HP] As HPPrice , [Apple] As ApplePrice, [microsoft] As microsoftPrice, [IBM] As IBMPrice from ( select Itemid,Vendorname,VendorPrice from @table)A PIVOT(MAX(VendorPrice) FOR Vendorname IN ([HP],[Apple],[microsoft],[IBM]))P ...
powershell script to create excel pivot table
There were a few issues with the creation of the pivot table and fields. First, it's nicer to select exactly which rows and columns you want in the table, without selecting the entire spreadsheet. A nice way to do this is to start with a range, then ask Excel to...
How to use PIVOT and JOIN together in SQL Server?
sql-server,join,sql-server-2012,pivot-table
You can simply perform a join in the source table of the PIVOT: Select ItemID, ItemPartNumber, ItemDescription, CreatedDate, InitialPrice, [HP] As HPPrice, [Apple] As ApplePrice, [Microsoft] As MicrosoftPrice, [IBM] As IBMPrice from ( select v.ItemID, VendorName, VendorPrice, ItemPartNumber, ItemDescription, CreatedDate, InitialPrice from VendorItemPricing as v left join MasterItems as m...
Pandas pivot gives ValueError
You have to use the pivot_table function: df = pd.pivot_table(df, index='customer_id', columns='category', values='cnt') And make sure the type is numeric: df = pd.DataFrame(data, dtype=float) ...
Laravel's Eloquent pivot object
laravel-4,eloquent,pivot-table
Use accessor: public function getSeasonAttribute() { return ($this->pivot) ? $this->pivot->season : null; } Then you can access it just like other properties: $position = $someModel->positions->first(); $position->season; if you also need it in the toArray/toJson output, then use on you model: protected $appends = ['season']; ...
Normalized MySQL Pivot / Rows to Columns without unique names
Here is an example using multiple LEFT JOINs. Joining the user is fairly simple. Joining the ingredients is a little more complex, as you have to join the RecipeIngredients table first and then use that id to join the ingredient. (note this only has the first 2 ingredients, the next/last...
Multiple criteria conditional formatting in a PivotTable
excel,table,pivot-table,excel-2013,conditional-formatting
Select the column to be highlighted and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true: =G1>F1 with red colour =G1<F1 with green colour leaves equal values without highlighting. If bothered by highlighting of...
Excel PivotTable; how to show values horizontally
There's a way to achieve that using array functions instead of pivot tables. Suppose your original data is located in A1:B7. To get the headers row (1, 2, 3): A10: =MIN(A2:A7) B10: =SMALL($A$2:$A$7,COUNTIF($A$2:$A$7,"<="&A10)+1) Then copy B10 as far right as you need to get all other values To get the...
(kdb+/q) Pivot table: date in rows, symbols in columns, last price as values
The function piv requires the key and pivot arguments to be a list. So modifying your naive call as follows gives us a result: q) piv[`q;(),`date;(),`sym;`price;f;g] date | obhmprice oijbprice mkjkprice nihdprice ldegprice mbgnprice jmmip.. ----------| -----------------------------------------------------------------.. 2009.01.08| 72.35531 28.9323 23.88535 12.21371 2.417089 49.45298 98.14.. 2009.01.07| 83.59946 6.036849 21.47751 78.8127...
Pivot Table to Reorganize Data
excel,excel-formula,pivot-table
If your data has a lot of IDs and years, a pivot table is the quickest way to get your first two columns, but the monthly breakdown will need to be done using formulas (e.g. index/match to pick up the FY value and divide by 12).
How can I concatenate values in an Excel pivot, as opposed to summing or counting
excel,concatenation,pivot-table
What you want is not possible from a PivotTable. However, if Bob is in A2 a formula in C2 like: =IF(A1=A2,C1&", "&B2,B2) and another in D2 of: =A2=A3 both copied down to suit, may serve if you "fill in the gaps" (either in the PT or with Go To Special,...
How to line up columns of a pivot table
sql,sql-server,database,pivot,pivot-table
May be you need to unpivot instead of pivoting. I will do this using cross apply with tables valued constructor. Performance wise this will be better than Union All if you have some more names SELECT [date],NAME, uptime, downtime FROM Yourtable CROSS apply (VALUES ('service',Services_Uptime,Services_Downtime), ('center',Centers_Uptime,Centers_Downtime) ) cs (NAME, uptime,...
What is the effective way to have a pivot-table having pandas dataset columns as its rows?
Some people like using stack or unstack, but I prefer good ol' pd.melt to "flatten" or "unpivot" a frame: >>> df_m = pd.melt(df, id_vars="y") >>> df_m.pivot_table(index="variable", columns="y") value y 0 1 variable 3d 0.020 0.010 address 0.008 0.173 all 0.098 0.509 length_total 250.000 626.700 make 0.183 0.048 our 0.123 0.482...
Pivot in MySQL - Show first and last values depending on datetime-column
mysql,group-by,pivot-table,greatest-n-per-group,groupwise-maximum
You could also use subqueries to find the row values of the first and last orders. This assumes that the first and last orders are according to entity_id. SELECT customer_email, COUNT(*) AS total_orders, SUM(grand_total) AS total_turnover, (SELECT created_at FROM orders WHERE entity_id = MIN(t.entity_id)) AS first_created_at, (SELECT created_at FROM orders...
How can I send 2 pivot tables (from two sheets) in an Email?
vba,email,excel-vba,pivot-table,outlook-vba
This should do what you are looking for. Just used a simple string with both ranges: (Don't forget to accept if it works for you) Sub Mail_Selection_Range_Outlook_Body() '... Dim rng As Range, rng2 As Range '... Set rng = Sheets("Supporttab").PivotTables(1).TableRange1 Set rng2 = Sheets("Other sheet").PivotTables(#).TableRange# '... .HTMLBody = RangetoHTML(rng) &...
PostgreSQL 9.3: Pivot table query
sql,postgresql,pivot-table,postgresql-9.3,table-functions
SELECT * FROM crosstab( $$SELECT grp.*, e.group_name , CASE WHEN e.employee_number IS NULL THEN 0 ELSE 1 END AS val FROM ( SELECT employee_number , count(employee_role)::int AS total_roles , (SELECT count(DISTINCT group_name)::int FROM employee WHERE group_name <> '') AS total_groups , count(group_name <> '' OR NULL)::int AS available , count(group_name...
creating a calculated field in excel pivot table based on an item in a column
excel,pivot-table,calculated-field
You do this with a Calculated Item. Here is an example that I think gets close to what you want. I selected one of the dates in order to get the menu to show up. Here is the result And here is a good reference about these. http://www.contextures.com/excelpivottablecalculateditem.htm...
how to create Pivot query for this?
sql,sql-server,sql-server-2008,pivot-table,powerpivot
You can use dynamic crosstab to achieve this. Read this article by Jeff Moden for reference. SQL Fiddle DECLARE @sql1 VARCHAR(4000) = '' DECLARE @sql2 VARCHAR(4000) = '' DECLARE @sql3 VARCHAR(4000) = '' SELECT @sql1 = 'SELECT [POINTER] = POINTER_RANGE' + CHAR(10) SELECT @sql2 = @sql2 + ' , ['...
Cannot update filter controls, PivotTables, PivotCharts, or cube functions
To fix this problem I needed to go Excel Options > Trust Center > Trust Center Settings > External Content. In External Content I had Security settings for Data Connections and set to Disable all Data Connections. Changing that to Prompt user about Data Connections fixed my problem. I don't...
SQL Pivot Table with Additional Column
php,mysql,codeigniter,pivot-table
If I understand correctly, just use a case statement: SELECT IFNULL(p.waybill_number, 'GrandTotal' ) AS Waybill, (CASE WHEN p.waybill_number IS NOT NULL THEN w.consignee END) as consignee, (CASE WHEN p.waybill_number IS NOT NULL THEN w.consignor END) as consignor, SUM(IF(p.payment_terms = 'prepaid', p.amount, NULL ) ) AS Prepaid, SUM(IF( p.payment_terms = 'collect',...