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
3
votes
1 answer

Calculate percent in spotfire cross table

I have a cross table where a person has a set of tasks and if the person has completed the task or not. I want to be able to add another column to the cross table that calculates the percent of tasks completed (Yes/Grand total). Is this…
Reed Avers
  • 31
  • 1
  • 2
3
votes
1 answer

Jasper report remove/hide empty group

Background: Jaspersoft studio 6. Have this crosstab: The dataset for the crosstab: select 'prod1' as prod, 'mon1' as month, 1 as val union all select 'prod1' as prod, 'mon2' as month, 1 as val union all select 'prod2' as prod, 'mon1' as…
thotwielder
  • 1,563
  • 7
  • 44
  • 83
3
votes
2 answers

Dynamic table created from CTE (parent/child)

If I have a very simple table called tree create table if not exists tree (id int primary key, parent int, name text); And a few rows of data insert into tree values (1, null, 'A'); insert into tree values (2, 1, 'B'); insert into tree values (3,…
mortenoh
  • 255
  • 3
  • 17
3
votes
2 answers

Combine crosstab function with DISTINCT ON

I have two tables details and data table. I already joined the two tables and the crosstab function is already done. I want to show only the latest data per serial. Refer to the current and desired output below. Question: How can I use DISTINCT ON…
tacticz03
  • 35
  • 6
3
votes
2 answers

PostgreSQL one to many pivot with crosstab

I am trying to build a one to many query in PostgreSQL that looks at a customer table, a transaction table and the result is a table showing which products the customer has purchased. table: customers id 1 2 3 4 5 table: purchases custid product 1…
fauxgt4
  • 33
  • 5
3
votes
1 answer

How to filter a crosstab created in pandas by a specific column

I have created a cross tabulation in pandas using: grouped_missing_analysis = pd.crosstab(clean_sessions.action_type, clean_sessions.action, margins=True).unstack() print(grouped_missing_analysis[:20]) Which leads to displaying: action …
Dhruv Ghulati
  • 2,976
  • 3
  • 35
  • 51
3
votes
1 answer

How to display a value of field in crosstab total row?

I have two problems with my Crosstab component. In my Crosstab I have total row at the beginning of tab and I want to display value from my field in each column. So I created field and put it in total cell. While compiling I've got error "field not…
Tomasz Iwaszko
  • 187
  • 3
  • 14
3
votes
1 answer

Creating a calendar like table using crosstab in postgres

I'm going through the Seven Databases in Seven Weeks book, and am sort of stuck on an extra work question. The question is: Build a pivot table that displays every day in a single month, where each week of the month is a row and each day name…
Jason St. Jacques
  • 383
  • 1
  • 3
  • 14
3
votes
1 answer

R - dplyr crosstable of melted paired data

I am wondering how to perform a crosstable using dplyr with melted data. My data looks like this. idmen sexe dip14_rec 1 0110008218 1 Uni 2 0110008218 2 Primary-Secondary 3 0110010366 1 …
giac
  • 4,261
  • 5
  • 30
  • 59
3
votes
1 answer

Crosstab Troubles ("return and sql tuple descriptions are incompatible")

I am trying to compute a crosstab on a table (happens to be a simple materialized view, but this shouldn't matter): user=# select * from data; region | date | sum --------+------------+----- East | 2010-06-30 | 22 East | 2010-01-31 | …
abhillman
  • 3,942
  • 1
  • 20
  • 21
3
votes
1 answer

Pivoting a non-numeric table in AWS Redshift

I have a table in amazon redshift that I want to pivot along one column. E.g. dim1 dim2 val x a 4s x b 5v y a 9l y b 3t Would turn into: dim1 a b x 4s 5v y 9l 3t Note that AWS Redshift does…
Logister
  • 1,852
  • 23
  • 26
3
votes
2 answers

Columns in rows in big datasets (PostgreSQL) --Transponse?

I am trying to restructure my big data set so I can process my data in a easier way. I have about 20 tables with the same data structure as the displayed input table. There is one for each year from 1996 to 2015. This is one of my input tables…
marius
  • 45
  • 6
3
votes
1 answer

Improved Mosaic Plot (like Heatmap or bubbles)

I've two Factors and I'm making a crosstab of it. x<-table(clean$VMail.Plan,clean$International.Plan) I actually would like to graphically represent it like this: mosaicplot(x,data=clean,color=2:4,legend=TRUE,las = 1) The normal mosaicplot is not…
amrrs
  • 6,215
  • 2
  • 18
  • 27
3
votes
2 answers

Weighting results in pandas crosstab

I would like to use a third column to weight results in a pandas crosstab. For example, the following: import pandas as pd df = pd.DataFrame({'A': ['foo', 'bar', 'foo', 'bar', 'bar'], 'B': [1, 1, 0, 0, 0], …
prooffreader
  • 2,333
  • 4
  • 21
  • 32
3
votes
3 answers

Oracle SQL Cross Tab Query

I have a table which has the following structure and sample data: ITEM LOC STOCK 0001 KS5 10 0001 KS6 30 0002 KS5 10 0002 KS6 20 I need to query cross tab so that I get ITEM KS5 KS6 0001 10 30 0002 10 20 The LOC…
Imran Hemani
  • 599
  • 3
  • 12
  • 27
1 2 3
…
99 100