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
-1
votes
1
answer
23
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 ...
0
votes
1
answer
40
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
0
answers
24
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 ...
- 15
0
votes
1
answer
13
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 ...
- 239
0
votes
0
answers
58
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
43
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
----------- --...
- 51
3
votes
0
answers
60
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
174
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
0
votes
0
answers
38
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
26
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
...
1
vote
0
answers
107
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) ...
- 169
0
votes
1
answer
305
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?
- 65
0
votes
0
answers
18
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 ...
- 1
-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-...
- 99
3
votes
2
answers
923
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-...
- 33
0
votes
1
answer
167
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 ...
0
votes
1
answer
132
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,
...
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,
...
- 11
3
votes
0
answers
49
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:
+----+------+-----+-----+-----+-----+-----+-----+-----+-----+-----+----...
- 131
0
votes
0
answers
52
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
4k
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,...
- 135
2
votes
1
answer
217
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 |...
0
votes
0
answers
14
views
Crosstab some columns to add rows
My dataset is like
profession degree 2010 2011 2012 2013 2014
nurse associate 5 15 10 9 8
nurse bachelor 20 10 15 12 16
I want to ...
- 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
...
- 123
7
votes
1
answer
528
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 ...
- 177
3
votes
2
answers
1k
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
...
- 31
-1
votes
1
answer
502
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 ...
- 25
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 ...
- 25
0
votes
0
answers
486
views
How to flatten/pivot "inner join" into a single row in a certain order?
Let's say I have 2 tables in MySql/MariaDb: competisions and participants. There can be multiple participants in one competisions.
I want to
a) do an inner join
b) sort the participants by score
c) ...
- 47
0
votes
1
answer
46
views
Combine output from 3 queries into a single query
I have the following three queries:
SELECT NAME,
ABS(sum(UNITS)) as WK1
FROM products
inner join stockdiary on stockdiary.PRODUCT = products.ID
where SUB_CATEGORY='LB40'
and DATENEW ...
- 11
1
vote
1
answer
327
views
Flatten a JSONB file - What function/query
In a bit of a pickle at work - I have a Jsonb which I need to flatten a file that has similar structure:
{
"Manufacturers":[
{
"manufacturer":"Tesla",
...
- 25
0
votes
0
answers
38
views
Pivot table but with cross product
I' currently in the following situation: I have a measurements table that looks something like this:
+----+-----------+-------+---------+
| id | germplasm | trait | m_value |
+----+-----------+-------+...
- 165
0
votes
1
answer
61
views
Can you create a new field in a rollup summary or does it require a nested query?
The result will be a table where the original table contents are 450,000 rows (or records) of precinct election results for each statewide race in Texas.
Here's my query the way it is:
select
UPPER(...
1
vote
1
answer
307
views
Use of PIVOT without temporary table
Problem summary:
I'm unfamiliar with PIVOT. I want the same outcome of the code below:
DECLARE @fillerUnits TABLE (
ReportHour datetime,
FillerName varchar(20),
FillerUnits int
)
INSERT ...
- 41
-1
votes
1
answer
49
views
Combining multiple rows into one row
I’ve used the following SQL query to pull out the following data:
Select
Employee_Department, Employee_ID, DateTime_From, Wage_From, DateTime_To, Wage_To
From
wage_tbl
Where
Employee_ID = 254 or
...
0
votes
1
answer
4k
views
how to split multiple rows in the same column into different columns depending on the data provided postgres
so this is what I have...
id
name
children
children's age
1
Rachel
john
20
1
Rachel
cell
10
1
Rachel
jay
15
2
Jereme
Les
17
2
Jereme
greg
5.5
2
Jereme
ven
27
but I'm trying to find a way to ...
- 23
1
vote
1
answer
198
views
Fetch Different Values in different column for a specific id
I have a data as shown below:
I want the output as shown below
in Postgress or in Excel.
- 11
-4
votes
1
answer
43
views
please explain the query
This looks like what I need as well. However, not being proficient in query making sense out of some of this.. so can you please clarify some things for me?
;WITH x AS <--- is this a comment?
( ...
- 1
0
votes
1
answer
73
views
Replace %S with Feature Value on Feature String Based on Feature Name and Partid
I need to write a select statement that will replace a %S string in the FeatureString column with the actual values stored in the same table for a given PartID.
With the following example data:
+------...
- 103
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 ...
0
votes
1
answer
142
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 ...
4
votes
2
answers
19k
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
votes
2
answers
332
views
What is a single-table relational database
I searched Google for the definition of a single-table relational database and found nothing but redirection to other database models. What's up? Why is this concept confusing? Every relational ...
3
votes
2
answers
5k
views
Use values from one column as column names and sum data from another table
I'm going to do my best to describe this. So I have 3 tables as follows:
project
id
title
department_id
1001
Project One
50
1002
Project two
50
metric
id
name
101
Number of exhibits
102
Number ...
- 33
0
votes
0
answers
55
views
How to get first row group by partid and compliance typeid and document type?
I work on SQL server 2014 I face issue I can't get only one row
based on part id and compliance type id and document type .
so if have part id as 3581935 and compliance type id 1 and document type
Web ...
- 147
2
votes
1
answer
2k
views
Transpose rows to column in postgresql
The following query gives me a list of 1222 distinct columns:
select distinct column_name from information_schema.columns where table_name like 'fea_var%';
I want to create one base table which has ...
- 31
0
votes
1
answer
543
views
Rollup multiple rows into one [duplicate]
I would like to preface this by saying that I'm relatively new to writing SQL. Please bear with my stupidity.
I'm trying to extract data from multiple tables onto a spreadsheet
I have 3 tables that ...
0
votes
1
answer
346
views
How to pivot using multiple columns in SQL Server?
I am having trouble creating pivot table that follows the schema described below.
For example, with the following table (not all records are shown here):
Route
Bus_Fare_Payment_Method
...
0
votes
1
answer
94
views
Pivot table in PostgreSQL by pivoting 10~20 values in a column
Is it possible to crosstab a table in PostgreSQL without having to define an "as" clause given by a tuple (column type)?
Example: The created table "t" would have only 3 columns '1'...
- 117
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