Questions tagged [pivot]
A pivot (or crosstab) converts row data into columnar data, and vice versa for an unpivot. Not all databases support explicit PIVOT syntax but the functionality can be implemented, often using decision logic (CASE statements, etc), aggregate functions, or an extension.
473
questions
0
votes
1
answer
32
views
Require assistance to write Query joining 3 tables
I have a table called form_submission_entries which contains data like this:
id
submission_id
field_id
value
1
1
1
John
2
1
2
Doe
3
1
3
27
4
2
1
Jane
5
2
2
Doe
6
2
3
23
The field_id ...
- 115
2
votes
1
answer
7k
views
How to set the column names dynamically in crosstab?
I have a table with key and value and I have saved the values as shown below
when I use the below query
select * from crosstab (
'select rowname,colname,max(cellval) from table_to_pivot group by ...
- 23
0
votes
0
answers
266
views
Converting Access PIVOT query to SQL Server
TRANSFORM Sum(QUANTIDADE) AS SumOfQUANTIDADE
SELECT SUPERVISOR, Sum(QUANTIDADE) AS Total
FROM REPORTING
WHERE (((REPORTING.FAMILIA)="TESTE1" Or (REPORTING.FAMILIA)="TESTE2" In (...
- 13
0
votes
1
answer
2k
views
How to transpose a single key-value table in Postgres?
I have. Key-Value table of two columns. I want to get keys as column names and values in individual columns (named as were keys named before). How to do such thing in Postgres ?
Is it possible to get ...
- 75
0
votes
1
answer
919
views
SQLite Aggregation then Transpose
I am currently uploading my solar inverter data to PVOutput using a Linux machine and a scratch built Perl script. I want to add the meter data from my enrgy supplier. I have an 87,000 row CSV file ...
1
vote
1
answer
3k
views
Group by latest date with conditions
I need your help!
Let's say I have this table:
Instance
Date
MetricID
Value
Marc
09/14/21
1
5
Marc
09/14/21
2
2
Marc
09/14/21
3
1
John
09/14/21
1
10
John
09/14/21
2
1
John
09/14/21
3
1
Marc
09/...
- 21
0
votes
2
answers
4k
views
SQL Pivot with multiple columns [duplicate]
I have a table as shown below
+-----+------- ---+------------+------------+
| Id | Location | DateIn | Dateout |
+-----+-----------+------------+------------+
| 117 | SHIP | 2021-09-01 | ...
- 101
1
vote
0
answers
44
views
Turning Mysql values row into columns by form_id and entry_id
I would like to understand how I can turn these rows into columns in MySql
This is how the DB is saved:
id
form_id
entry_id
meta_value
1
7
18
Name
2
7
18
Date
3
7
18
Phone
4
7
18
Email
5
7
18
...
- 11
1
vote
1
answer
3k
views
In SQL Server, is there a way to pivot on an unknown number columns WITHOUT using dynamic SQL?
Essentially if we have this:
create table #temp ([key] nchar, [value] nvarchar(2));
insert #temp ([key], [value]) values
('a', '1'),
('b', '2'),
...
...
- 157
0
votes
0
answers
1k
views
How to use PIVOT or LISTAGG to solve this?
I have a table like below.
USERID
USERNAME
ITEM
12345
JESSE
ITEM1
12345
JESSE
ITEM3
12345
DON
ITEM2
12346
DANNY
ITEM4
12346
ELISA
ITEM1
12347
ANDY
ITEM2
12347
ANDY
ITEM4
What im trying to ...
- 1
1
vote
2
answers
3k
views
Can I bring two values from a single row into a Pivot Table
I am trying to get two values from rows in a table into a single row in a pivot table.
myTable has the following rows
ID1
ID2
Item
Value
SP
11
22
Item 1
10
10
11
22
Item 2
100
100
11
22
Item 3
49
...
1
vote
2
answers
53
views
Pivoting table in MySQL
I was wondering if it is possible to change the output of this:
User Database Select Insert Update Delete Create References Alter Drop
------------- ------------------...
- 13
0
votes
1
answer
933
views
Combining multiple SELECT/WHERE into a result with multiple columns with alias names
I have a Timescale DB / PostgreSQL table (DataTable) which looks like this
I'm trying to find a query which would return different columns for different subsystem/metrics combination like this
...
0
votes
0
answers
636
views
SQL server IN Clause usage with Pivot table
I have a problem with pivoting a table on MSSQL.
The main problem is, when I want to fetch row values in an inner query (with IN clause) I get syntax errors but when I type the values statically, it ...
- 101
0
votes
1
answer
386
views
Pivot email rows into multiple columns per id
I have a table "emails":
id
email
1
[email protected]
2
[email protected]
2
[email protected]
4
[email protected]
2
[email protected]
4
[email protected]
I need this result:
id
email
email
email
1
[email protected]
2
[email protected]
[email protected]
[email protected]
4
[email protected]
[email protected]
the number ...
- 3
-4
votes
1
answer
1k
views
Horizontal to vertical table
I have this query
SELECT quiz_id, COUNT(case when options LIKE '%question_id_8":"1%' then 1 else null end) as 'answered 1',
COUNT(case when options LIKE '%question_id_8":"2%' then ...
0
votes
1
answer
51
views
Pivoting a two-column table to show if a permutation exists
Struggling with the terminology here...
I have a table of elements which may or may not be present in a group:
elem | grp
----------
1 | A
1 | B
2 | A
3 | C
I'd like to turn this into a ...
- 101
0
votes
1
answer
2k
views
SQL Server PIVOT returns only one row [duplicate]
I have a simple table of of 2 columns(varchar):
name |occupation
___________________
Sandra |Medic
Julija |Actor
Marija |Actor
Martyna |Manager
Aiste |Teacher
Kristina|Teacher
Virg |Teacher
...
0
votes
2
answers
602
views
Select All Field (if field not exist, make field with null value)
I have a table with this field and condition
table cc_queue_detail
ticket_id field_name_id data_content
1 category_ticket_1 33
1 category_ticket_2 ...
3
votes
1
answer
320
views
Properly pivot an XML column with XQuery
I would like to pivot the attributes and values of an SQL XML column and display it in an ordered columns and rows format.
To get started, this is what the XML column looks like in SQL:
<Model ...
- 55
0
votes
0
answers
128
views
Generate report by cross join between two tables
I wanted to generate report between two tables by using CROSS JOIN.
Below shows the details of table.
Table 1: kod_jawatan
jawatan_id
jawatan_kod
jawatan_nama
Table 2: tpermohonan
mohon_id
...
- 1
0
votes
1
answer
169
views
SELECT JOIN same table prevent duplicates MySQL 8.x
I have a table called 'price' with the following rows
idRequest | idPair | idExchangePlatform | lastPrice
1 1 1 95
1 1 2 ...
0
votes
0
answers
21
views
Help with SQL query, should I use Pivot
I have a table that returns data like
ID
Code
Error
Date
VB1
VB2
VB3
VB4
2
E1
Uncap Error
2021-02-25
8
0
0
9
3
E1.2
Recap Error
2021-02-25
3
0
0
6
4
E2
Recap Error
2021-02-26
1
1
0
0
5
E3
Tip ...
- 11
2
votes
2
answers
209
views
MSQL - Switching rows to columns
I've been stuck on the following for quite a while, it seems like it should be relatively simple but I can't quite get my head round it.
- 23
0
votes
1
answer
819
views
How to apply transpose method in MYSQL, Getting rows as collumns
have a table of ideas that stores responses from evaluators and stores as one row per evalautor For ex : Evalautor 1 evaluated idea A then in table record gets added as
IDEA A, COMMENTS , EVALUATOR ID
...
1
vote
3
answers
93
views
Filter table by multiple pivot values
I have the following tables:
fabrics
| id| title | code|
|-- |---------| ----|
| 1 | Fabric 1| FBR1|
| 2 | Fabric 2| FBR2|
| 3 | Fabric 3| FBR3|
| 4 | Fabric 4| FBR4|
attributes
| id| title | ...
- 121
0
votes
2
answers
70
views
Pivoting 4 by 44 result set
I have a SQL Server query that gets the sales data for a 44 store market chain, it includes 4 values for each store (Sales, COGS, GP and GP Margin), I declared a CTE and joined it 44 times for each ...
- 152
2
votes
1
answer
590
views
How to PIVOT data from MSSQL query and Fetch to PHP view
I have a MSSQL 2005 Database with query content like this :
nopas
code
name
order
00115301
D031
ADAM
1
00130015
D031
ADAM
2
00149998
D026
JAMES
1
00149970
D028
FIKI
1
00142641
D028
FIKI
2
...
- 21
1
vote
2
answers
908
views
Dynamic pivot table filtering and performance
I have these tables and I'm trying to rotate the subscriber to column table to horizontal and filter its result based on multiple and/or conditions like the following:
WHERE first_name LIKE 'm%' AND ...
- 113
2
votes
2
answers
106
views
Oracle Procedure alter pivoted data to unpivot and push into different destination
I have a PL/SQL procedure which takes string at runtime which is comma separated,splits it and pushes into table.
Example of procedure and table is given here:
Procedure is push_data.
Current table ...
- 101
-1
votes
1
answer
1k
views
Write a dynamic query of nested subqueries [duplicate]
Let's take an everyday query with a list of nested queries so I can show you the problem, see how we'd have to add a subquery when a new Certification comes out:
select DISTINCT e.display_name as &...
- 201
0
votes
2
answers
5k
views
MySQL - Get counts of `car models` by `car make`
I have a table with cars data, i.e. Makes, Models, & Year.
I want to get the count for each Car with how many models it has... something like the image below:
This is the SQL query I am using ...
- 111
-1
votes
1
answer
3k
views
Convert columns to rows without using pivot [duplicate]
I have been assigned to get the data in required format from two tables.
TableStaff :
STaffID | Staff Name
--------------------
1 | John
2 | Jack
and TableLead
LeadID | LeadValue | ...
- 123
0
votes
1
answer
138
views
SQL Query - Pivot with Ranges
This is my sample table
BSEG_ID DST_ID BILL_SQ AUDIT_CALC_AMT BILL_SQ, MSR_PEAK_QTY_SW, DESCR_ON_BILL, VERSION, AUDIT_CALC_AMT
12334 REV-WSC 62 6.40667 For 0 - 60 (Next 62.000 cubic ...
- 1
0
votes
2
answers
170
views
Getting averages based on column values
Editing because I made a mistake transforming from my real example with lots of irrelevant details to the boxes example:
I have a table BOXES like this:
box_id | color | dimension | value
1 ...
2
votes
1
answer
1k
views
SQL: pivot non numeric data [duplicate]
I have a set of data which basically looks like the following:
+---------+--------+
| EventId | Field |
+---------+--------+
| 1 | apple |
| 1 | paper |
| 1 | boy |
| 2 | ...
- 23
-2
votes
2
answers
953
views
Turning columns result set into independant rows in MySQL
I am pretty new to this and have been struggling over one issue.
I have a result set which for each Opportunity in a table (id as primary key), provides a date for a first cashflow (DFCFW) as a column ...
1
vote
2
answers
391
views
Build a select query that includes aggregated values from a different table
I have two tables in Postgres: polls and votes.
The first one polls is designed to contain the poll-related data. Every poll is aimed to have only two possible responses — option_a and option_b
+----+-...
0
votes
1
answer
2k
views
Merge multiple rows containing different values into one row
I have a database with the structure:
Region | OpenServicesID | DFC
K00001 | 1400 | 4
K00002 | 1300 | 3
K00001 | 1200 | 4
K00001 | 1100 | 4
...
- 9
1
vote
1
answer
147
views
MySQL DB design for very long growing row size
My data has the property to grow in the row direction and the column direction at the same time. The size will grow in both directions without limit while I do more research.
The number of columns ...
- 113
1
vote
1
answer
95
views
Create A Pivot With No SUM() in MySQL
I've seen a lot of examples using MySQL to create a PIVOT but they all use SUM() - using this DDL how can I create a PIVOT in MySQL
Create Table `data`
(
callDate text
,callerName text
,...
0
votes
0
answers
17
views
Change Data In Field To Row Header [duplicate]
I have this mysql ddl
Create Temporary Table #empData
(
workDate varchar(100)
,numOfCustomers int
,numOfSales int
,avgTimeToGreetCustomer varchar(100)
);
Insert Into #empData Values
('07/01/...
2
votes
1
answer
13k
views
One to many relationship query result in a single row and dynamic multiple columns
In a one-to-many relationship query, I would have a single row and dynamic multiple columns.
In this example the expected result is to have for each owner all the associated phone numbers in a single ...
- 33
1
vote
1
answer
119
views
MySQL convert rows to column
I am working with 2 tables and need help to produce an output by converting rows to columns, and i need to sum the value first be grouping
Here is the fiddle: https://www.db-fiddle.com/f/...
- 11
0
votes
1
answer
725
views
How I can flatten a count table in postgresql that contains only 2 columns with crosstab?
In my database I have the following table:
Mytable
id SERIAL,
category VARCHAR,
value VARCHAR
And I execute the following query:
select category, COUNT(*) from mytable group by category
What I want ...
0
votes
1
answer
299
views
2 Level pivot using Postgresql
I have a table whose schema along with data (table_name : raw_data) appears to be this :
name | category | clear_date |
A | GOOD | 2020-05-30 |
A | GOOD | 2020-05-30 |
A | GOOD |...
- 103
0
votes
1
answer
22
views
SQL query to return result with different row
I am working with a database that has a table called 'order' that looks like the following.
id day item
-------------------
11 1 apple
11 3 banana
11 4 berry
...
- 11
2
votes
2
answers
5k
views
How to Pivot Multiple Columns in SQL Server
I've read many posts about using pivot to get the data in the format I want, but the more I read the more confused I get.
I have this data:
That I'm trying to get into a format similar to this:
For ...
- 33
0
votes
1
answer
173
views
How to split a SUM column based on another BIT column? [duplicate]
MySQL v5.7
Having this table:
id | sprId | load | calc
-----|----------|---------|---------
1 | 1 | 1 | 7
2 | 2 | 1 | 1
3 | 2 | 1 | ...
- 115
0
votes
1
answer
66
views
Pivot on datetime with matching multiple column
I have the following table with column school, state, subject and timedate.
I would like to have output with column school, state, act and english
filter subjects to only Act and English
pivot table ...
- 1