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.
474
questions
0
votes
1
answer
275
views
Exporting an Excel Spreadsheet containing a pivot table and charts to MySQL
I am looking at moving a large Excel spreadsheet to an SQL database - most likely MySQL.
The excel spreadsheet contains 12 worksheets, with one of them being the Master, and the other 11 set up to ...
CommunityBot
- 1
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
...
CommunityBot
- 1
0
votes
0
answers
5
views
Power Pivot/Data model issue
Good day,
I am facing two blocking points where I would need your insights please.
I have downloaded all my sheets in power query and made the necessary transformations, including creating a common ...
1
vote
1
answer
33
views
Oracle multiple PIVOT clauses
I have data with 2 calculated bucket columns I would like to summarize with a PIVOT query. This is an example set of pre-generated data. My bucket columns here are sec and pct.
select '1' id, 'a' ...
- 4,819
0
votes
2
answers
2k
views
complete transpose a table with dynamic rows and column
I have a mysql database with this structur:
Name
2019
2020
2021
2022
...
Name1
124
98
34.5
NULL
Name2
102
NULL
34
NULL
Name3
34
56
97
123
Name4
NULL
NULL
34.5
NULL
...
...
Every year comes new ...
CommunityBot
- 1
1
vote
4
answers
3k
views
Can Group By and Count on MySQL be optimized?
I have a 100 million row table on MySQL. I need to count rows for certain ranges and I have the proper indices for filtering rows. Let's say a SELECT statement returns 20000 results, but all I need is ...
- 77k
0
votes
2
answers
44
views
Crosstab of multiple columns in postgresql
I'm trying to combine two crosstab in postgres but stuck it will be great if someone guide me.
-- rainfall CROSS TAB
SELECT * FROM
CROSSTAB(
'SELECT block_id, forecast_date, rainfall_mm
FROM weather....
- 172k
8
votes
1
answer
4k
views
MySQL query to create a Matrix view using pivot
I have a table with the following fields:
p1
p2
s1
s2
where p1 -> player1, p2 -> player2, s1 -> game1, s2 -> game2
The number of players can vary from 3 - 10.
The table have the results between them....
CommunityBot
- 1
0
votes
1
answer
821
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
...
CommunityBot
- 1
0
votes
1
answer
2k
views
Oracle convert multiple rows to column using pivot
I have a table that contains the values
+-------------+--------------+---------------------+-------------+-------------+--------------+-------+---------+-----------+---------------+--------------+
|...
CommunityBot
- 1
3
votes
2
answers
2k
views
Merging multiple rows into one in MySQL
I have the following table :
Column1
Column2
Column3
Column4
Account
One
Two
Three
Data1
Acc1
One
Two
Three
Data2
Acc2
One
Two
Three
Data3
Acc3
Five
Six
Seven
Data3
Acc4
Five
Six
Seven
Data1
...
CommunityBot
- 1
0
votes
1
answer
789
views
MySQL - Split results of count function into columns derived from a separate column
The title seems a bit confusing, but I am struggling to put down what I need this query to do so best to explain it. I have 3 tables in my database (Using MySQL Workbench), but for this query I'm just ...
CommunityBot
- 1
-1
votes
1
answer
35
views
Pivoting 2 columns into a rows
I hope one can help me with this issue, I have a list of personnel and each one of them have same/different skills I need someone to help me to create a SQL script to change it
I have these two ...
- 3,780
0
votes
1
answer
289
views
MySQL show data from Rows to columns
I am stuck at getting a query with joining 2 tables. Below is the database structure.
What I want to achieve is to list all the information from one table with join on the second table but it has 3 ...
CommunityBot
- 1
0
votes
1
answer
616
views
Convert rows to column for every nth row by ID
I'm trying to convert rows to columns.
I have the following columns: user_id, meta_key, meta_value
I have the following rows: 'first_name', 'last_name', 'stillingsbetegnelse'
Right now I can ...
CommunityBot
- 1
1
vote
1
answer
3k
views
Converting rows to columns using crosstab in PostgreSQL not working (relation "table" does not exist)
I need to use the compiled data using CTE and then convert the columns to rows using crosstab(open to other ideas) in the next select statement. Below is the query.
with checked_adgroup AS (
...
CommunityBot
- 1
0
votes
1
answer
49
views
MS SQL Dynamic Pivot from Stored Procedure result
I have 3 tables as Student, Batches and StudentAttendanceRegistry. I managed to join these tables and get a desired result as following,
StudentID Name Year Month ...
0
votes
1
answer
302
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 |...
CommunityBot
- 1
0
votes
0
answers
29
views
Is posible to create column dynamicaly based on query select using group by before
Good afternoon, i want to ask about MySql query.
I have the initial data from first query as pict below (can dymically) base on where clause using this query SELECT company, discipline_name from ...
- 35.2k
0
votes
1
answer
18
views
Name a column with a string from the row
I have the following query, which works fine:
select
Produkt.Name,
max(case when Substanz.ID == 1 then Inhalt.Menge end) as "Histidin",
max(case when Substanz.ID == 2 then Inhalt.Menge ...
- 1,423
0
votes
0
answers
96
views
PIVOT for "groups" - rows to columns
How-to convert rows (for group) into columns.
Here is the source table that have:
update: added Id and IdGestion columns
Id IdGestion PeticionId Field Value
48 17 16 Type ...
- 145
-1
votes
1
answer
76
views
Fastest way to get a intrinsic cartesian product of grouped data
Consider table:
CREATE TABLE dbo.DIMENSIONS (
dim_order int,
dim_name varchar(50),
member_key varchar(50)
)
SELECT * FROM dbo.DIMENSIONS
dim_order dim_name member_key
----------- --...
- 61
3
votes
0
answers
64
views
How to create a view that returns all child records in a single row without aggregation?
I am an electrical engineer developing a parts library database and explorer tool for internal use in my organization. If anyone is familiar with OrCAD schematic capture tools, I am essentially ...
- 31
2
votes
1
answer
249
views
SQL query to change rows into columns based on the aggregation from rows
I have a table, "MyTable" as below:
tool
team
metric
date
tool1
team1
25
1/1/2023
tool1
team1
10
2/1/2022
tool2
team2
20
1/2/2022
I want to aggregate "metric" periodically by ...
- 23.1k
3
votes
2
answers
4k
views
Rotating a table with multiple value columns
I'm not sure if this can be done with crosstab(). I've been using this questions/answer as a reference, but am having difficulty putting together the query.
My table looks like this, just that I have ...
- 172k
5
votes
2
answers
22k
views
How to transpose values from one column into columns with a value from a different column?
I have a database with the following structure:
Date
role
type
duration
2022-04-16
Nurse
Food preparation
45
2022-04-17
Nurse
Cleaning
30
2022-04-17
Volunteer
Cleaning
20
2022-04-17
Nurse
Food ...
1
vote
0
answers
27
views
Select rows as columns with Postgres [duplicate]
I have this situation, I have a table with name->value columns like this
NAME
VALUE
ip
12.12.12.12
name
Smith
db
base
pass
22222
I would like to select this table like this
ip
name
db
pass
...
0
votes
0
answers
49
views
Join PIVOT query in SQL
How can I group pivot query by the category. I can achieve this in Excel but I wanted to do in SQL Oracle query.
Let say we have data like this:-
I can simply pivot to get grade count by subject
...
1
vote
0
answers
141
views
Create PIVOT table with MariaDB's CONNECT engine
Given the following base table, which implements a sort of Entity-Attribute-Value table
CREATE OR REPLACE TABLE dipendenti_detail (
id_dipendente int(10) unsigned DEFAULT 0,
id_azienda int(10) ...
- 167
0
votes
1
answer
402
views
How can I pivot column headers into rows?
I would like to get a list of column headers for a table.
I have queried
SELECT TOP 0 * FROM TABLE_NAME.
But now I want to pivot those column names into rows. How do I do that?
- 83k
0
votes
0
answers
19
views
queryMultiple columns in one single row mysql
select employee,
log_type,
DATE_FORMAT(time,"%Y-%m-%d") as "attendance_date",
case when log_type = 'IN' then TIME_FORMAT(ch.time,"%H:%i:%s") end as check_in ,
case when ...
- 77k
-1
votes
1
answer
25
views
Need to get results grouped by fields and column value as name
I have table as below
reported_date
url
stautus_code
country
2022-01-20
example.com
200
India
2022-02-29
example.com
200
US
2022-01-20
example.com
200
France
2022-01-20
sample.com
200
India
2022-...
- 77k
6
votes
3
answers
8k
views
How to pivot data in SQLite
I've a table which contains text data containing a bunch of translations for different language. Each translation is for a specific label.
I need to generate a pivot table so to get quickly what's ...
3
votes
2
answers
1k
views
How can I compute the TOTAL of multiple COLUMNS from a PIVOT?
I have the following ORACLE SQL table/data:
A
DATE_FIELD
C
D
1
01-JAN-13
hello
0
2
01-FEB-13
wonderful
10
3
20-MAR-13
world
20
4
21-APR-13
tree
10
5
21-JUN-13
tree
30
6
01-JUL-13
world
10
7
30-...
- 22.8k
10
votes
4
answers
7k
views
Dynamically convert hstore keys into columns for an unknown set of keys
I have a database that stores a bunch of custom fields using hstore. In order to merge it into another database that doesn't support hstore, I'd like to split the keys into extra columns.
Users can ...
- 273
0
votes
1
answer
168
views
Merge rows values if the FK is same
I have a following table
PK
NAME
FK
01
abc
01FK
02
bca
01FK
03
cab
02FK
04
xyz
03FK
05
yzx
02FK
I want to generate a new table where FK is the primary and the rest of the column(s) merge based ...
- 83k
0
votes
1
answer
188
views
Join result of crosstab to main query
I'm trying to get this column structure:
| id | name | main_image | rating | shop_names | prices | urls | 2 | 3 |
My table structure looks like this
CREATE TABLE products (
id SERIAL PRIMARY KEY,
...
- 172k
1
vote
1
answer
32
views
Grouping results belonging to the same product number into the same record
I have a very long query that I have shortened and anonymized so the problem becomes clearer.
Here is an approximation of my query:
SELECT subquery1.productNumber,
subquery1.anotherinfo,
...
- 193
0
votes
1
answer
241
views
Dynamic Pivot Table syntax error
What's wrong with this Dynamic Pivot table query? I'm staring myself blind on this.
mysql> SET @sql = NULL;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT
-> GROUP_CONCAT(DISTINCT
...
CommunityBot
- 1
3
votes
0
answers
53
views
Dynamically swap multiple rows with database columns
First let me start by saying that I've done intensive research before asking here. I have a table datatable that looks like this:
+----+------+-----+-----+-----+-----+-----+-----+-----+-----+-----+----...
- 77k
0
votes
0
answers
59
views
SQL data transpose query?
How to convert long data to wide data without hard coding in SQL?
If any one please help me.
Regards
Manish Bose
0
votes
1
answer
5k
views
MS SQL Server pivoting without aggregation
I have this table:
CREATE TABLE Sales (idx int, variable varchar(50), value varchar(50));
INSERT into Sales (idx, variable, value) VALUES(1, 'column1', '34');
INSERT into Sales (idx, variable,...
- 2,146
0
votes
1
answer
151
views
Converting EAVT table into SCD type 2
After a lot of research and head picking, I'm still unable to find a good/clean solution to convert an entity-attribute-value-timestamp table to an scd type 2 dimension.
Here's the issue:
I have a CRM ...
- 11
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/...
CommunityBot
- 1
2
votes
1
answer
252
views
What is the most efficient way, to create this output, from a single SQL table?
In short: I would like to use this input:
+---+---+---+
| x | y | z |
+---+---+---+
| 1 | 1 | a |
| 1 | 2 | b |
| 1 | 3 | c |
| 2 | 1 | d |
| 2 | 2 | e |
| 2 | 3 | f |
| 3 | 1 | g |
| 3 | 2 | h |
| 3 |...
CommunityBot
- 1
-1
votes
1
answer
2k
views
How to combine multiple rows into one, based on event id, datetime, and an action into their own columns?
I'm not sure how to word the topic title...
I have a table with data similar to below, where each row stores an event associated with alarms. For example, events might include:
alarm goes active
...
- 4,391
7
votes
1
answer
583
views
What am I missing in my crosstab query here?
I am having difficulty understanding how to correctly transpose this table from a database I am using. This table was created by someone who designed the database years ago, but I managed to retrieve ...
CommunityBot
- 1
0
votes
1
answer
171
views
Vertical data set to horizontal data set for many columns
I have read about pivot function but I am not sure if If it helps me in this situation. Essentially I have to produce my vertical data set horizontally. For instance the first table shows the vertical ...
CommunityBot
- 1
1
vote
1
answer
1k
views
Transpose 1 row into columns with count/sum
I have a a single row with age buckets as column names. I want them all in one column with their count or sum whatever I end up doing. I have quite a few buckets but for the sake of this example I've ...
- 172k
-1
votes
1
answer
560
views
Convert the categorical values in a column into columns themselves (Transpose)
Experts,
I have this data -
ID | buckets | SUM (USD)|
--------- --------------- ---------
1 | 10 - 20 | 500 |
--------- --------------- ---------
1 | 30 - 40 ...
CommunityBot
- 1