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...

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...

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...

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...

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...

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)),...

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,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: ...

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...

Try this On Error Resume Next For i = 10 To 0 Step -1 .PivotItems(i).Visible = False Next i On Error GoTo 0 ...

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,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...

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...

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) ...

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...

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...

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,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',...

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%"...

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.

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...

Update merely updates the layout - it doesn't refresh the data.

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...

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;...

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: ...

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 =...

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,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...

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...

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...

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...

excel,sharepoint-2010,web-parts,pivot-table

We were able to resolve the problem by restarting the Excel Services component of Sharepoint 2010.

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...

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...

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...

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...

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...

Instead of using a calculated field like this, put another Value in your pivot table and make it the Average of Ordered.

excel,vba,excel-vba,pivot-table

currMonth is initialized as: currMonth = "Jan-14" but should be: currMonth = "='Jan-14'" Do you see the difference?...

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...

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...

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,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....

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...

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 =...

Just like @pnuts said, but here's a full-blown example. ...

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...

python,r,excel,dictionary,pivot-table

In Excel I would use a PivotTable, about 15 seconds to set up: ...

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...

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 ...

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...

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(); ...

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 ...

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 =...

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-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...

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,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...

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...

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...

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.

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,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)...

>>> 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...

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...

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...

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...

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...

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...

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]+'...

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...

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(); ...

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 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".

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...

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...

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,...

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 ...

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...

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...

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-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']; ...

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...

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...

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...

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...

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).

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,...

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,...

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...

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...

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) &...

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...

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...

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 + ' , ['...

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...

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',...