How to parse records to html-table in django templates
django,templates,html-table,crosstab
Generally in Django the philosophy is to reshape your data in the view first, keeping the templates as simple as possible (rather than having complicated reshaping code in the templates) That said, in your case you can get a long way with this: https://docs.djangoproject.com/en/dev/ref/templates/builtins/#regroup pass ['A', 'B', 'C'] into your...
Group By and add columns
I tend to approach this type of query using conditional aggregation: select ward, max(case when seqnum = 1 then councillor end) as councillor1, max(case when seqnum = 2 then councillor end) as councillor2, max(case when seqnum = 3 then councillor end) as councillor3 from (select wc.*, row_number() over (partition by...
PostgreSQL 9.3: Crosstab query
postgresql,postgresql-9.3,crosstab
Maybe this is what you want? select x1.*, x2.colc, x2.cold from crosstab ( $$select x1.cola, x2.colb, CASE WHEN x1.colb = x2.colb THEN 1 ELSE 0 END FROM testing x1 CROSS JOIN testing x2 $$ ) AS x1( cola VARCHAR(10), City1 INT, City2 INT, City3 INT, City4 INT) LEFT JOIN testing...
How to reformat a dataset using the values of rows as new columns?
postgresql,pivot,crosstab,data-analysis
If the number of possible test_id is fixed and known the easiest way to do this is to use conditional expressions like this: select id, max(case when test_id = 'a' then 1 else 0 end) as a, max(case when test_id = 'b' then 1 else 0 end) as b, max(case...
To compute sum regarding to a constraint
sql,postgresql,aggregate-functions,crosstab
I think you have the conditions backwards in the query: SELECT p.partner_id, SUM(CASE WHEN pa.currency_id = 1 THEN amount ELSE 0 END) AS curUsdAmount, SUM(CASE WHEN pa.currency_id = 2 THEN amount ELSE 0 END) AS curRubAmount, SUM(CASE WHEN pa.currency_id = 3 THEN amount ELSE 0 END) AS curUahAmount FROM public.player_account...
SQL SUM Down Rows and Sum of Sum Across Columns
sql,sql-server,sql-server-2008,sum,crosstab
select sum(FinPeriodNr) as FinPeriodNr , sum(BalJan) + sum(BalFeb) + sum(BalMar) + sum(BalApr) as TotalSumofBal FROM Tble ...
Sub Report /Main report cross tab fields difference,
It is difficult to manuplate these kind of calculations in cross tab.. but one way around would be. Approach 1: As I see your cross tabs are simple hence instead of sub report use both corss tabs in main report it self. For difference (Surpless or deficit) use 3rd cross...
Crosstab in pandas with enforced values
That is a pivot_table: >>> pv = df.pivot_table(index='A', ... columns=['B', 'C'], ... aggfunc='size', ... fill_value=0) >>> pv B Happy Sad Very Happy C False True False True A 1 0 0 1 1 3 0 1 1 0 4 1 0 0 0 The columns/rows which do not appear there...
How to apply scales::percent or scales::percent_format() to prop.table in R to format numbers as percentages
r,formatting,plyr,percentage,crosstab
pt <- percent(c(round(prop.table(tab), 3))) dim(pt) <- dim(tab) dimnames(pt) <- dimnames(tab) This should work. c being used here for its property of turning a table or matrix into a vector. Alternative using sprintf: pt <- sprintf("%0.1f%%", prop.table(tab) * 100) dim(pt) <- dim(tab) dimnames(pt) <- dimnames(tab) If you want the table written...
Generate crosstabulations from dataframe of categorical variables in survey
Here, we are getting the frequency for each column by using lapply and table. lapply gets the data.frame in a list environment and then use table after converting the column to factor with levels specified as 0:5. Use, prop.table to get the proportion, cbind the Freq and Percent, convert the...
How do I order weekdays with row and column total in dynamic pivot
sql-server,sql-server-2008,pivot,crosstab,dynamic-pivot
SELECT employeeid, SUM(CASE WHEN datename(dw,entrydate)='MONDAY' THEN hours END) as Monday, SUM(CASE WHEN datename(dw,entrydate)='TUESDAY' THEN hours END) as Tuesday, ....., sum(hours) as TotalHours FROM Project_TimeSheet WHERE year(entrydate)='2014' and month(entrydate)='12' GROUP BY employeeid WITH ROLLUP ...
Postgres - Transpose Rows to Columns
sql,database,postgresql,crosstab,transpose
Use crosstab() from the tablefunc module. SELECT * FROM crosstab( $$SELECT user_id, user_name, rn, email_address FROM ( SELECT u.user_id, u.user_name, e.email_address , row_number() OVER (PARTITION BY u.user_id ORDER BY e.creation_date DESC NULLS LAST) AS rn FROM usr u LEFT JOIN email_tbl e USING (user_id) ) sub WHERE rn < 4...
Using crosstab to convert rows to columns fails
You must provide a second parameter with the list of possible values to allow for missing values in each resulting row: SELECT * FROM crosstab( 'SELECT id, key, value FROM tbl ORDER BY 1, 2' , 'SELECT generate_series(1,25)' -- assuming your key is type integer ) AS ct(id text ,...
Transform long rows to wide, filling all cells
sql,postgresql,postgresql-9.1,crosstab,generate-series
In order to get the current location for each business_id for any given year you need two things: A parameterized query to select the year, implemented as a SQL language function. A dirty trick to aggregate on year, group by the business_id, and leave the coordinates untouched. That is done...
How to hide Crosstab Column Header in XLS report - Crosstab and Subreport in Summary Band
java,jasper-reports,xls,crosstab
I will answer to my own question. After many attempts, due to a lack of time, I made a new report for XLS output format. (Now I have two nearly same report, one for PDF and HTML, and other for XLS output format) In report I have unchecked Crosstab Properties...
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...
Dynamic pivot query using PostgreSQL 9.3
postgresql,pivot,crosstab,postgresql-9.3
SELECT * FROM crosstab( 'SELECT ProductNumber, ProductName, Salescountry, SalesQuantity FROM product ORDER BY 1' $$SELECT unnest('{US,UK,UAE1}'::varchar[])$$ AS ct ( "ProductNumber" varchar ,"ProductName" varchar ,"US" int ,"UK" int) ,"UAE1" int); Detailed explanation: PostgreSQL Crosstab Query Pivot on Multiple Columns using Tablefunc Completely dynamic query for varying number of distinct Salescountry? Dynamic...
PostgreSQL 9.3:Dynamic Cross tab query
postgresql,pivot,crosstab,postgresql-9.3
A server-side function cannot have a dynamic return type in PostgreSQL, so obtaining the mentioned result as-is from a fixed function is not possible. Also, it does not look much like a typical crosstab problem, anyway. The cola part of the output can be obtained by filtering over an aggregate,...
Postgres - PivotTable/crosstab with more than one value column
One way would be to use a composite type: CREATE TYPE i2 AS (a int, b int); Or, for ad-hoc use (registers the type for the duration of the session): CREATE TEMP TABLE i2 (a int, b int); Then run the crosstab as you know it and decompose the composite...
Crosstab two columns under one column header Crystal Report
crystal-reports,dataset,crosstab
First I add NPCalculated column in Summarized field and now I have two summarized fields. Next, right-click one of the summary cells > Summarized Field lables > Summarize horizontally.
Printing the data in horizontal order. Dynamic columns
jasper-reports,crosstab,subreport
We have to use crosstab in ireports to get dynamic columns
SQLSERVER Select rows as columns even if there is no enough rows
SAMPLE TABLE CREATE TABLE #TEMP(ColumnIDToGroup INT,Value VARCHAR(30)) INSERT INTO #TEMP SELECT 1, 'AAAA' UNION ALL SELECT 1, 'BBBB' UNION ALL SELECT 2, 'AAAA' UNION ALL SELECT 2, 'BBBB' UNION ALL SELECT 2, 'CCCC' Now select the rows from the table, create a column for Value1, Value2 etc and select the...
Crystal Reports Crosstab Layout - columns heading Not displyaing [closed]
You need to put them in the Page Header Section, not in Report Header. (if I get your question)
PIVOT VIEW using PostgreSQL
sql,postgresql,pivot,case,crosstab
Your query works like this: SELECT * FROM crosstab( $$SELECT "timestamp", name , CASE name WHEN 'sensor3' THEN value::numeric * 1000 -- WHEN 'sensor9' THEN value::numeric * 9000 -- add more ... ELSE value::numeric END AS value FROM source ORDER BY 1, 2$$ ,$$SELECT unnest('{bar_18,foo27,sensor1,sensor2,sensor3}'::text[])$$ ) AS ( "timestamp" timestamp...
How to create table where columns in first table are fields in another table?
In PostgreSQL you can install the tablefunc extension: CREATE EXTENSION tablefunc; In that extension you will find the crosstab(text, text) function. This is an ugly function to work with (putting it mildly), but it will do what you want: SELECT * FROM crosstab( 'SELECT t1_id, name, month, coalesce(amount, 0) FROM...
R: Non-onerous method of getting CrossTable (gmodels) results nicely formatted (html) into markdown document
r,markdown,knitr,crosstab,rmarkdown
Give a try to the pander general S3 method instead of kable: > pander(CrossTable(esoph$agegp, esoph$alcgp, digits = 1)) ------------------------------------------------------------ 0-39g/day 40-79 80-119 120+ Total ------------ ----------- -------- -------- -------- -------- **25-34**\ \ \ \ \ \ N\ 4\ 4\ 3\ 4\ 15\ Row(%)\ 27%\ 27%\ 14%\ 5%\ 17% Column(%)...
How to do a crosstab with two categorical variables but populate it with the mean of the third variable
Try library(reshape2) acast(diamonds, cut~color, value.var='price', mean) # D E F G H I J #Fair 4291.061 3682.312 3827.003 4239.255 5135.683 4685.446 4975.655 #Good 3405.382 3423.644 3495.750 4123.482 4276.255 5078.533 4574.173 #Very Good 3470.467 3214.652 3778.820 3872.754 4535.390 5255.880 5103.513 #Premium 3631.293 3538.914 4324.890 4500.742 5216.707 5946.181 6294.592 #Ideal 2629.095 2597.550...
Can't save dynamic crosstab in table
sql,sql-server,dynamic,crosstab
After sweating a lot I found a solution for my problem. Although it is still ugly I've change from insert command to update command. I've use ID column, since #TEMPVAR is create from #TEMPUF. The revised @CMD below. Thanks anyone for your time and effort! 21 SET @CMD = N'DECLARE...
How do I combine two columns into single column in dynamic pivot
sql-server,sql-server-2008,pivot,crosstab,dynamic-pivot
Here is your table create table demo( id varchar(max), val decimal(4,2), month int, year int, decider int ) INSERT INTO demo ([id], [val], [month], [year], [decider]) VALUES (101, 0.25, 11, 14, 411), (101, 1, 12, 14, 411), (101, 0.5, 1, 15, 411), (101, 0.75, 2, 15, 411), (102, 0.25, 11,...
Better way to produce data frame using table()
Your code modified as a function would be efficient compared to the other solutions in base R (so far). If you wanted the code in one-line, a "reshape/table" combo from base R could be used. reshape(as.data.frame(table(df)), idvar='x', timevar='y', direction='wide') # x Freq.failure Freq.success #1 0 3 2 #2 0.1 3...
150x150 crosstab in stata, showing timeseries movement between categories
tabout from SSC may work for you: clear set more off *----- example data set ----- input /// id year occup 1 1999 1 1 2000 1 1 2001 1 2 1999 1 2 2000 2 2 2001 1 3 1999 1 3 2000 2 3 2001 2 4 1999...
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',...
How transform rows to column in mysql
mysql,pivot,group-concat,crosstab
You need some kind of dynamic sql. In MySql it can be done using Prepared Statements Below is a simple example - assumming that results of your query is saved into a temporary table named your_query_goes_here SET @sql = ( SELECT concat( 'SELECT `nome`, ', ( SELECT group_concat( DISTINCT concat('min(if(...
How do I address run-time error '3265' with VBA in my Access form?
sql,vba,ms-access,access-vba,crosstab
You do not need to make a TRANSFORM for this OR create a query. Just add some code to the OnChange events of your comboboxes, checking if both comboboxes have a valid value. If so, fill the txt_Muffin_Count like this: Me.txt_Muffin_Count = _ DCount("RowID","TableA","Product = 'Muffin' " & _ "AND...
How to make a crystal report with following view or Postgres crosstab() function?
database,vb.net,postgresql,crystal-reports,crosstab
After trying a lot i myself found a solution for this, making a crosstab crystalreport seems not good to me so i will explain what i have done is: Installed the additional module tablefunc which provides the function crosstab(). Since i am PostgreSQL 9.1 i can use CREATE EXTENSION for...