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.
84
questions with no upvoted or accepted answers
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....
- 181
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
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:
+----+------+-----+-----+-----+-----+-----+-----+-----+-----+-----+----...
- 131
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
...
- 31
3
votes
0
answers
1k
views
Query to split data in a column into multiple columns using id in another column (pivot table; crosstab)
Database engine:
Server version: 10.2.16-MariaDB-10.2.16+maria~xenial mariadb.org binary distribution
I have a database table defined as follows:
CREATE TABLE `disktemp` (
`sample_time` datetime ...
- 93
3
votes
0
answers
4k
views
how to get a sum total of each column and row - postgresql [Crosstab or Case]
I have to count the records according to the criterion, they are much more filters but I have summarized.
I could use either of two ways.
1#
SELECT * FROM crosstab('SELECT dim_espacio.desc_estab, ...
- 31
3
votes
0
answers
2k
views
Efficient way to transpose large categorical column to one-hot table row
I have a varchar column containing ~ 8000 unique values and 500k rows. I need the column transposed so that the column headers are the unique values and the values in the rows are 1 where the row has ...
- 131
3
votes
0
answers
609
views
Why is dynamic pivoting not supported?
TL;DR What is the reason that in 2013 we still can't define a VIEW that displays some pivoted data with a dynamic column set.
TL;DR/2 Is pivoting data a denormalization technique?
First: Not going ...
- 2,420
3
votes
1
answer
5k
views
How can we improve this PIVOT operation? ( SQL Server 2008 R2 )
We have this query, that runs in 1:10. We need to improve this. This is just a test query, but we will implement this upgrade on our real query.
select top 100 *
from (
select codCliente
,...
- 7,374
2
votes
1
answer
66
views
How do I pivot to summarize my entries by a specific area?
I have a map where every row represents a bird and a row with the attributes shown in the excerpt of the attribute table:
round | area | date | species | behaviour
--------------------...
- 21
2
votes
2
answers
62
views
Calculating the number of times a motive was cited over a given period
I'm using PostgreSQL 9.4. I have the following surveys table. Each row includes a motive that was cited by the respondent, represented by the motive_value column (think of it as motive_id).
----------...
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
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
121
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
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 (
...
1
vote
0
answers
757
views
How to properly name a database table
First of all, I understand this question may seem opinion-based, but I know there are some standards for naming conventions and I would like to know which is the best and why. It's not so complex ...
- 111
1
vote
2
answers
44
views
Add data from rows to different columns if more than 1 row have a partial match
I tried to find a solution but do not find anything to add multiple columns from partially matching rows.
Data:(Maximum of 3 programs per person)
name surname program
Jan Peters ...
1
vote
1
answer
38
views
Preparing a query to paste into excel to get growth by month
I have a table that looks like this:
|DB | DATA_MB | LOG_MB | |
| DB1 | 4,4375 | 2,25 | 2019-08-06 10:37:49.160 | |
| DB2 | 8 | 0,5 | 2019-08-06 10:37:49....
- 7,374
1
vote
0
answers
44
views
Repivoting rows of pivoted data with an dynamic amount of rows
I am trying to create an overall sequential pivot of various rows of data, repeating each row as a new set of columns and using the sequence number as the trailing identifier in each column name. Here ...
- 11
1
vote
0
answers
140
views
Dynamic rows and columns with Pivot and calculated value
I'm trying to build a pivot request but i don't undestand how it's applicable for my database. The value I need has to be calculate (A percentage) for each column and row.
I'm using SQL Server 2012.
...
- 11
1
vote
0
answers
38
views
need help with multiple column aggregate using pivot in mssql
below is the temporary table for class assessments. this table contain list of class assessment which include the assessment code, date of assessment, total item and passing percentage.
create table #...
1
vote
0
answers
1k
views
How to transposing rows into columns in MySQL?
I have three table (student's_info,subject and marks):
this is table student's_info:
----------------
|s_id | s_name |
----------------
| 1 | Ali |
| 2 | Jon |
---------------
This is ...
- 23
1
vote
0
answers
32
views
Tier Dimension based on measure value
I have a case where i need to have a dimension based on measure value.
example :
the fact table is linked to date dimension through DateID,
and what i want to add is another dimension for Tier, but ...
- 11
1
vote
0
answers
1k
views
MySQL dynamic number of columns
I'm looking for a way to dynamically build up the number of columns in a query.
Imagine a school assessment database.
I desire a way to get, for each question the percentage of students who got a ...
- 262
1
vote
1
answer
38
views
Specify order of attributes in crosstable
I'm trying to create pivot tables on PostgreSQL.
I successfully created my first example. But I have on problem:
I use the two-parameter form of crosstab, which takes a query for the data and a ...
- 153
1
vote
0
answers
42
views
Pivot table with date_shutdown and factory_name and type_shutdown and time_shutdown
I need to make a pivot table that includes a factory name column along with a column for every type of shutdown; in the intersection the period of the shutdown. Factory names and shutdown types are ...
- 11
1
vote
0
answers
130
views
how to edit kvp data as crosstab/pivot
I am keeping data in key/value pairs. I know how to convert that data into crosstab form using queries like this:
<!-- language: lang-sql -->
SELECT master.tag,
MAX(CASE WHEN data.key = ...
- 11
1
vote
0
answers
7k
views
Grouping and Transposing Rows to Columns in PostgreSQL
I have the follwing situation: I have profiles and tasks. One profile can be used once for each task. I track usage in profiles_used table. I need to build a report that shows how many profiles of ...
- 509
1
vote
0
answers
765
views
MS Access 2010 report - SQL pivot query WHERE criteria not working
I have created a report that has a list of actions which are populated from an "Actions" table. (See Below)
However, each action will have multiple updates populated from an "Updates" tables and I ...
- 113
1
vote
0
answers
783
views
SQL: Dynamic Pivot Column, In the column's row value How to get Top 1 value
In my following query I need to get the top 1st record's value in pivot row value. So basically I get all parts and if there is a service entry with that part and a revision number I used that ...
- 11
1
vote
1
answer
50
views
MYSQL Database PIVOT and wants in specific format
I have below table and its data in mysql
CREATE TABLE `pointhistory` (
`id` bigint(20) NOT NULL auto_increment,
`channelid` varchar(64) NOT NULL,
`deviceid` varchar(64) NOT NULL,
`pointid` ...
- 11
1
vote
0
answers
1k
views
Partly rotated table with pivot/dynamic SQL without aggregation
Using MS SQL 2008, I have created a table that I want to display in horizontal direction instead of vertical direction. Because I do not now how many query results I will get returned I think I have ...
- 35
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 ...
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
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 ...
- 27
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 ...
- 239
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
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
...
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 ...
- 1
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
0
answers
545
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
0
answers
41
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
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
0
answers
62
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
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
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
0
votes
0
answers
642
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
0
answers
132
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