FAQ Database Discussion Community


Postgres - PivotTable/crosstab with more than one value column

sql,postgresql,crosstab
I have a view that produces the following resultset: client_id asset_type current_value future_value 1 0 10 20 1 1 5 10 1 2 7 15 2 1 0 2 2 2 150 300 And I need to transform it into this: client_id a0_cur_val a0_fut_val a1_cur_val a1_fut_val ... 1 10 20...

How to reformat a dataset using the values of rows as new columns?

postgresql,pivot,crosstab,data-analysis
I have a dataset that looks like this: id | test_id ---+-------- 1 | a 1 | b 1 | u 2 | a 2 | u 3 | a 3 | b 3 | u And I would like to roll it up into a new table such that...

How to apply scales::percent or scales::percent_format() to prop.table in R to format numbers as percentages

r,formatting,plyr,percentage,crosstab
Consider the following example: tab <- table(mtcars$vs, mtcars$cyl, dnn = c("vs", "cylinder")) prop.table(tab) # cylinder # vs 4 6 8 # 0 0.03125 0.09375 0.43750 # 1 0.31250 0.12500 0.00000 round(prop.table(tab)*100, 1) # cylinder # vs 4 6 8 # 0 3.1 9.4 43.8 # 1 31.2 12.5 0.0 Desired...

150x150 crosstab in stata, showing timeseries movement between categories

time-series,stata,crosstab
I'm a bit over my head here and I hope you can help me, or at least point me in the right direction. I got a massive dataset (5.8 mio observations per year, over 14 years), which deals with individuals' occupation over time. I need to sum up the changes...

PostgreSQL 9.3: Crosstab query

postgresql,postgresql-9.3,crosstab
I have a table called as testing which is having four columns as shown below. create table testing ( cola varchar(10), colb varchar(10), colc varchar(10), cold varchar(10) ); Insertion of some rows: insert into testing values('A1','City1','X1','Z1'), ('A2','City2','X2','Z2'), ('A3','City3','X3','Z3'), ('A4','City4','X4','Z4'); Expected Result: I want to show the result which looks like...

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

Crosstab in pandas with enforced values

python,pandas,crosstab
I get some data samples, e.g, df = pd.DataFrame({'A':[1,1,3,3,4], 'B':['Very Happy','Sad','Sad','Happy','Happy'], 'C': [True,False,False,True,False]}) >> df A B C 0 1 Very Happy True 1 1 Sad False 2 3 Sad False 3 3 Happy True 4 4 Happy False And want to compute counts for each combination, so crosstab is...

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

Group By and add columns

sql,postgresql,pivot,crosstab
I have a table named 'ward_councillors' and two columns 'ward' and 'councillor'. This table contains a list of all of the councillors and the ward they are responsible for. Each ward has three councillors and what i'd need to do is group by so that I have one distinct record...