Stack Overflow
Stack Overflow

Questions tagged [crosstab]

A cross tab, also known as a "pivot table", is a method of summarizing data in tabular form. Typically, these have a category column in the left hand side, and multiple summary columns to the right. Each different category will have its own row. Be sure to also include the relevant language tag (python, R, sql-server etc.) on your question.

Resources:

1549 questions
7
votes
3 answers

How to do a crosstab with two categorical variables but populate it with the mean of the third variable

library(ggplot2) data(diamonds) str(diamonds) ## 'data.frame': 53940 obs. of 10 variables: ## $ carat : num 0.23 0.21 0.23 0.29 0.31 0.24 0.24 0.26 0.22 0.23 ... ## $ cut : Ord.factor w/ 5 levels "Fair"<"Good"<..: 5 4 2 4 2 3 3 3 1 3…
Ajay Ohri
  • 3,382
  • 3
  • 30
  • 60
7
votes
3 answers

Dynamic pivot query using PostgreSQL 9.3

I have a table named as Product: create table product ( ProductNumber varchar(10), ProductName varchar(10), SalesQuantity int, Salescountry varchar(10) ); Sample values: insert into product values ('P1', 'PenDrive', 50, 'US') ,…
MAK
  • 6,824
  • 25
  • 74
  • 131
7
votes
5 answers

How to run RAW SQL query in PhalconPHP

I try to get result from this query $sql = " SET @col = NULL; SET @sql = NULL; Select Group_Concat(Distinct Concat( 'SUM(CASE WHEN tbl.sdate…
Sir Z.
  • 124
  • 1
  • 1
  • 4
7
votes
2 answers

How do I achieve a pivot report in Crystal Reports for Visual Studio?

I am a rank noob at any reporting but horizontal lines with sub-totals. I have a dataset that comprises the times spent by cars in a carpark. I must produce a table showing a column for each hour and a row for each day, showing the number of entries…
ProfK
  • 49,207
  • 121
  • 399
  • 775
7
votes
2 answers

Sum by month and put months as columns

Background I have time series data on a monthly basis and I would like to sum values for each ID, grouped by month and then have the month names as columns rather than as rows. Example +----+------------+-------+-------+ | id | extra_info | month |…
Pete Hamilton
  • 7,730
  • 6
  • 33
  • 58
7
votes
2 answers

Row Percentages in crosstable generated from summary() from the Hmisc package

I have been trying to learn to use the summary()-function from the Hmisc-package to generate crosstables that include chisquared tests. With help from this board I'm almost there. I just can't figure out how to obtain row-percentages instead of…
Rene Bern
  • 545
  • 3
  • 10
  • 18
7
votes
3 answers

Crosstab with a large or undefined number of categories

My real problem has to do with recording which of a very large number of anti-virus products agree that a given sample is a member of a given anti-virus family. The database has millions of samples, with tens of anti-virus products voting on each…
user1761471
  • 71
  • 3
6
votes
4 answers

How to pass parameters in crosstab?

Can anyone tell me, how to pass parameters to crosstab?
lee
  • 61
  • 1
  • 1
  • 3
6
votes
2 answers

Enable grow of cross tab in crystal report

I am using a Cross-Tab object in crystal report. I am having problem making my data column (Field Row) to increase dynamically in height when my data size increases. Since the "Can Grow" property in the Format Editor is disabled, it would not let…
Zain Ali
  • 15,535
  • 14
  • 95
  • 108
6
votes
2 answers

Postgresql transpose rows to columns

I have this query select * from sales shop | date | hour | row_no | amount -----------+------------+-----------+--------+----------- shop_1 | 2012-08-14 | 00:08:00 | P01 | 10 shop_2 | 2012-08-12 | 00:12:00 | O05 …
MG N
  • 61
  • 1
  • 2
6
votes
2 answers

Convert rows to column in PostgreSQL

I want to convert rows to column in PostgreSQL.I want all variables against their respective id.but its not working. Expected Output: myvar desc fname lname sdate edate id title1 desc1 cina jhon …
nagi
  • 381
  • 2
  • 8
  • 22
6
votes
1 answer

Crosstab function in Postgres returning a one row output when I expect multiple rows

I currently have a table m of the following format: id scenario period ct 2 1 1 1 2 1 2 1 2 1 3 1 2 1 4 1 2 2 1 1 2 2 …
Stevie Kobori
  • 63
  • 1
  • 4
6
votes
2 answers

Pivot / crosstab with more than one value column

I have a view that produces the following resultset: CREATE TABLE foo AS SELECT client_id, asset_type, current_value, future_value FROM ( VALUES ( 1, 0, 10 , 20 ), ( 1, 1, 5 , 10 ), ( 1, 2, 7 , 15 ), ( 2, 1, 0 , 2 ), ( 2,…
Rodrigo Strauss
  • 2,064
  • 2
  • 14
  • 14
6
votes
4 answers

What's a good data model for cross-tabulation?

I'm implementing a cross-tabulation library in Python as a programming exercise for my new job, and I've got an implementation of the requirements that works but is inelegant and redundant. I'd like a better model for it, something that allows a…
Chris R
  • 17,546
  • 23
  • 105
  • 172
5
votes
3 answers

Transposing an sql result so that one column goes onto multiple columns

I'm trying to get data out of a table for a survey in a particular format. However all my attempts seems to hand the DB because of too many joins/too heavy on the DB. My data looks like this: id, user, question_id, answer_id, 1, 1, 1, …
Yule
  • 9,668
  • 3
  • 51
  • 72
1 2 3
…
99 100