Tagged Questions
0
votes
1answer
18 views
Postgresql change column to row in a generic way
A key value pair(column name, value) needs to be retrieved from a table with about hundred columns.
The following query does exactly what is needed.
With dummy AS (
Select 1 as Col1, 2 as Col2
)
...
0
votes
1answer
59 views
Use Where condition instead of aggregate function with group by
I have as table called calendar table, Which have columns like year, month, week, date, and day. day field have values like 1,2,....7.
I want to create time span from it. like one span from Monday to ...
1
vote
2answers
63 views
SQL:Select columns based on rows of another table
How do I create a sql select statement where the select column names are values in rows of another table (I am using postgresql)? I have a language table with language codes:
language
========
id ...
1
vote
3answers
73 views
Select value as column
If I have a table containing:
game powerup used
memory 1 12
memory 2 10
bejeweled 2 88
bejeweled 3 54
...where the (number of) different possible ...
0
votes
1answer
64 views
Dynamically generate the AS clause for Postgres crosstab
This is actually a follow up to Dynamically generate columns for crosstab in PostgreSQL
To be clear: if I have a dataset with a variable number of keys I cannot generate the AS clause?
Eg.
A table ...
1
vote
2answers
60 views
SQL query for sales report by date
I have a table of sales leads:
CREATE TABLE "lead" (
"id" serial NOT NULL PRIMARY KEY,
"marketer" varchar(500) NOT NULL,
"date_set" varchar(500) NOT NULL
)
;
INSERT INTO lead VALUES (1, ...
1
vote
2answers
87 views
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
...
1
vote
2answers
114 views
SQL: Turning Rows into Columns for Variable Number of Rows
I have two tables whose simplified structure looks like this:
RESPONSES
id
created
ACCESSORY VALUES
id
response_id
sensor_id
value
I want to create a view that flattens all accessory values for a ...
1
vote
2answers
301 views
Merge several columns from two tables into one rows for id in the former table each
If there are a table A and table B. The structure of them are below:
A :
id
1
2
B:
col_1 col_2
m q
n w
How Can I get the results of C which is below by SQL?
id ...
6
votes
2answers
504 views
Dynamic alternative to pivot with CASE and GROUP BY
I have a table that looks like this:
id feh bar
1 10 A
2 20 A
3 3 B
4 4 B
5 5 C
6 6 D
7 7 D
8 8 D
And I want it to look ...
1
vote
3answers
169 views
Pivot on Multiple Columns using Tablefunc
Has anyone used tablefunc to pivot on multiple variables as opposed to only using row name? The documentation notes:
The "extra" columns are expected to be the same for all rows with the
same ...
2
votes
2answers
71 views
complex query in postgresql
Hello and warm greetings to you all,
I am having some challenges writing a complex query in posgresql. the fact is i'm having problem writing this particular query period, and your help is kindly ...
2
votes
1answer
183 views
Postgres - Crosstab maybe?
I have a table A with 3 columns-
date | type | value
_________________________
2012-01-01 | 1 | 100
2012-01-01 | 2 | 200
2012-01-02 | 1 | 200
2012-01-02 | 2 | 300
2012-01-03 | 1 ...
0
votes
1answer
176 views
postgres crosstab / transpose
I would like to ask for help.. I have this table
user_id | Metric_1 | Metric_2 | Metric_3
------------------------------------------
1 | Jan | 10 | yes
2 | Feb | 10 ...
2
votes
4answers
211 views
SQL Select to combine row values in output table using Pivot or CASE
I am using a PostgreSQL database and have a table as follows:
--------------------------------------
| Date | MetricType | MetricValue |
--------------------------------------
| date1 | MetricA ...
4
votes
4answers
293 views
How do I transpose rows and columns (a.k.a. perform a pivot) in PostgreSQL only for rows and columns with a minimum COUNT()?
Here's my table 'tab_test':
year animal price
2000 kittens 79
2000 kittens 93
2000 kittens 100
2000 puppies 15
2000 puppies 32
2001 kittens 31
2001 kittens 17
2001 puppies ...
1
vote
2answers
65 views
Repeat a column, duplicating it into more identical columns in the same row
I have table with 3 columns like this: (ID, city,city_pop). I just want copy city_pop column 20 times. So the final table will look like something this: (ID, city, city_pop, city_pop1, city_pop2, ...
2
votes
1answer
145 views
How can I break down query result into separate columns?
Following up from my previous question:
Pixel values of raster records to be inserted in the table as columns
Imagine my query result has 5300 row as results like:
+-------+
|value |
+-------+
| ...
1
vote
1answer
87 views
Pixel values of raster records to be inserted in the table as columns
I have a table with following columns:
(ID, row_num, col_num, pix_centroid, pix_val1).
I have more than 1000 records. I am inserting my data using:
insert into pixelbased (row_num, col_num, ...
1
vote
2answers
957 views
How to transpose columns and rows in PostgreSQL (i.e., how do I switch rows and columns)? [duplicate]
Possible Duplicate:
Transposing an sql result so that one column goes onto multiple columns
I'd like to do a sort of row/column swapping in my PSQL database. Here's my example database:
id ...
-1
votes
1answer
141 views
How to GROUP BY into separate columns
I have 3 tables:
The first one contains information about persons. The relevant column is the personID.
The second contains exercises a person can do. There are for example 3 exercises with an ...
4
votes
2answers
191 views
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, ...
6
votes
2answers
466 views
How to do pivot table without knowledge of columns
I have read most of the posts on stackoverflow on how to do a pivot table but all of the posts show examples with prior knowledge of the columns. How do you construct a query if you have no knowledge ...
1
vote
1answer
547 views
Unique Rows to Columns in Postgresql
I have table of data that is sorted as follows:
Item | Sample | Value |
---------------------------------
Part A | Top | 1.0 |
Part A | Bottom | 4.0 |
Part A | ...
0
votes
1answer
198 views
postgres crosstab query with $libdir/tablefunc crosstab_hash function
My crosstab query (see below) runs just fine. However, I have to generate a large number of such queries, and - crucially - the number of column definitions will vary from day to day. If the number of ...
3
votes
2answers
667 views
Collapse rows into one column
I have a table with the following layout:
ID Label Value
-- ----- -----
1 Lab1 Value1-1
1 Lab2 Value1-2
1 Lab3 Value1-3
1 Lab4 Value1-4
1 Lab5 Value1-5
1 ...
1
vote
2answers
72 views
Aggregate several values from a field, depending on a code from another field
I need to calculate several values from a field in a table depending on a code in another field of the same table.
The table is in a PostgreSQL 8.3 database.
Table:
cod_1 | cod_2 | date | ...
0
votes
3answers
250 views
quotation mark incorrect when using crosstab in PostgreSQL
I have a table t1 as below:
create table t1 {
person_id int,
item_name varchar(30),
item_value varchar(100)
};
There are five records in this table:
person_id ====item_name ====== item_value
...
0
votes
1answer
198 views
sql query to set data as column heading
I have a problem that is deceptively difficult! Lets say I have a DB table that looks like this:
student_id score
502 0
502 2
502 4
407 3
407 1
407 3
...
2
votes
1answer
3k views
PostgreSQL and pivot tables using crosstab function
I have a problem with creating a pivot table in PostgreSQL using the crosstab() function. It works well but it produces multiple records for the same client_id. How can I avoid this?
Here is the SQL:
...
3
votes
1answer
955 views
Display multiple values of a column in one row in PostgreSQL
I have a query like this:
select to_date(to_char(registime, 'YYYY-MM'),'YYYY-MM') as dt,count(id) as total_call
from all_info
where alarm_id is null
group by dt
order by dt
And the result ...
2
votes
2answers
124 views
Window function inside a pivot, is it possible?
I have this table
And I need to create a pivot, that displays emotions as columns, with average emotion_level grouped by user_id, user_date, emotion. For example, for user_id = 1, user_date = ...
0
votes
1answer
218 views
Struggling with pivoting a dataset from rows to columns
I have a dataset that has mutliple records representing different stats for the same entities.
Example:
DEVICE METRIC SCORE WHEN
-------------------------------------------------
devA ...
3
votes
1answer
233 views
making simple self join more efficent
I have a table
id|level|name
level can be 1,2 or 3
what I want to get is:
id|lvl1name|lvl2name|lvl3name
I'm using the following query
SELECT L1."name" as lvl1name, L2."name" as lvl2name, ...
11
votes
4answers
10k views
PostgreSQL Crosstab Query
Does any one know how to create crosstab queries in PostgreSQL?
For example I have the following table:
Section Status Count
A Active 1
A Inactive 2
B Active 4
...
3
votes
6answers
6k views
correct way to create a pivot table in postgresql using CASE WHEN
I am trying to create a pivot table type view in postgresql and am nearly there! Here is the basic query:
select
acc2tax_node.acc, tax_node.name, tax_node.rank
from
tax_node, acc2tax_node
where
...
13
votes
3answers
14k views
SQL Transpose Rows as Columns
I have an interesting conundrum which I believe can be solved in purely SQL. I have tables similar to the following:
responses:
user_id | question_id | body
----------------------------
1 | 1 ...
0
votes
3answers
998 views
mysql pivot to a postgres pivot table
I was using mysql just fine until I recently switched one of my rails apps to heroku and had to change over. Almost everything works as expected except I have one query which does something totally ...
0
votes
1answer
1k views
rails db neutral pivot table or crosstab
Does anyone know of a way to build a pivot table using activerecord which would be remotely DB neutral? I've tried to avoid using find_by_sql and DB specific queries but for a pivot table or crosstab ...
