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

PostgreSQL crosstab with dynamic column names and multiple input columns

Problem I've got a PostgreSQL 9.6 database with a table designed according to an EAV model with different types of values. An example excerpt looks like this: name |arrivalTime | boolValue | intValue | floatValue |…
user711270
  • 107
  • 1
  • 7
4
votes
1 answer

DynamicReports crosstab report column grouping breaks after calling setDataPreSorted(true)

I'm using crosstab report and after applying row and column sorting, it breaks column grouping. I call setDataPreSorted(true) on crosstab and SortBuilder for row and column: SortBuilder rowSortBuilder = asc(field("rowOrder",…
digz6666
  • 1,798
  • 1
  • 27
  • 37
4
votes
2 answers

Turning arbitrarily many rows into columns in PostgreSQL

I have a table in Postgres that was designed to capture information in unstructured form and rebuild it. I need to re-apply some structure when exporting data from that table and am struggling. Currently, I have a table of the form: lbl | name …
George
  • 187
  • 1
  • 2
  • 12
4
votes
1 answer

Is there a way to pivot rows to columns in MySQL without using CASE?

There are lots of posts out there on pivoting rows into columns for various databases. They seem to fall into two camps, using case statements or using a built in function of the database vendor. I am using MySQL and have not found anything so far…
Scott
  • 16,711
  • 14
  • 75
  • 120
4
votes
3 answers

Sorting in Crosstab sort column/row group with Order by expression

I know there is a similar thread here already, but that doesn't work for me. Background: Jaspersoft Studio 6 I have a data set like this: Created a crosstab like this on this dataset: How to sort the column group to prod c, prod a, prod b instead…
thotwielder
  • 1,563
  • 7
  • 44
  • 83
4
votes
1 answer

How to create a cross tab (in crystal) from multiple columns (in sql)

I have 5 columns in SQL that I need to turn into a cross tab in Crystal. This is what I have: Key | RELATIONSHIP | DISABLED | LIMITED | RURAL | IMMIGRANT ----------------------------------------------------------------- 1 | Other Dependent…
J Lyne
  • 87
  • 8
4
votes
1 answer

MySQL Crosstab aggregation with 2 conditions

I have a query which creates a crosstab. The results are a count of the txn_id for branda, and the count of txn_id for brandb. The txn_id is NOT UNIQUE. This is an example of the transactions table.: txn_id | nationality_id | sku | sales | units 1…
Adam Copley
  • 1,495
  • 1
  • 13
  • 31
4
votes
3 answers

SQL convert row values to column headers

I have the following table: tableA +-----------+--------+ | tableA_id | code | +-----------+--------+ | 1 | code A | | 2 | code B | | 3 | code A | | 3 | code C | | 3 | code B | | 4 | code A | | …
user740521
  • 1,175
  • 4
  • 12
  • 25
4
votes
1 answer

Postgres Crosstab Dynamic Number of Columns

In Postgres 9.4, I have a table like this: id extra_col days value -- --------- --- ----- 1 rev 0 4 1 rev 30 5 2 cost 60 6 i want this pivoted result id extra_col 0 30 60 -- --------- -- -- …
allenwlee
  • 665
  • 6
  • 21
4
votes
1 answer

Postgresql 9.3: How to use crosstab with multiple indexes?

Here is a sqlFiddle that shows what I'm trying to do. Here is @lad2025 sqlFiddle that shows it better I have two indexes on my table plus a column with the column names and a column with the values. In the fiddle I show a query that does what I…
Borbag
  • 597
  • 4
  • 21
4
votes
0 answers

Using compound assignment pipe operator with CrossTable output for example

I would like to make use of the compound assignment pipe operator %<>%. For example using the code below I can generate a simple data frame with the required proportions: data("mtcars") Vectorize(require)(package = c("magrittr", "gmodels"), …
Konrad
  • 17,740
  • 16
  • 106
  • 167
4
votes
3 answers

Complex SQL query to one table

There is a table: event |id |timestamp --------------------- event1|001|21-03-15 event2|001|22-03-15 event1|002|23-03-15 event2|002|24-03-15 What should be a request to display the result: id |event1 |event2 …
venom1_
  • 53
  • 6
4
votes
3 answers

Calculating columns with row percentage after obtaining sums by group in dplyr

Using dplyr I'm generating a simple summary table for two categories: # Data data("mtcars") # Lib require(dplyr) # Summary mt_sum <- mtcars %>% group_by(am, gear) %>% summarise(n = n()) %>% spread(key = am, value = n) Which produces the…
Konrad
  • 17,740
  • 16
  • 106
  • 167
4
votes
2 answers

PIVOT VIEW using PostgreSQL

I'm new to PostgreSQL and am using version 9.4. I'm having a table with collected measurements as strings and need to convert it to a kind of PIVOT table using something which is always up-to-date, like a VIEW. Furthermore, some values need to be…
lucas0x7B
  • 336
  • 1
  • 3
  • 12
4
votes
2 answers

PostgreSQL merge two queries with COUNT and GROUP BY in each

1st query: SELECT date_trunc('day', date1) as date, COUNT(*) AS count_a FROM table_a GROUP BY date result: date count_a 2014-04-01 00:00:00.0 1011642 2014-04-02 00:00:00.0 309048 2nd query: SELECT date_trunc('day', date1)…
Dawid Moś
  • 827
  • 2
  • 12
  • 18
1 2 3
…
99 100