Skip to main content

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
Filter by
Sorted by
Tagged with
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....
dimoss's user avatar
  • 181
3 votes
0 answers
97 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 ...
jde0503's user avatar
  • 31
3 votes
0 answers
64 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: +----+------+-----+-----+-----+-----+-----+-----+-----+-----+-----+----...
JohnTheWalker's user avatar
3 votes
2 answers
3k 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 ...
dbase964's user avatar
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 ...
Mausy5043's user avatar
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, ...
raul's user avatar
  • 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 ...
o1lo01ol1o's user avatar
3 votes
0 answers
682 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 ...
Martin's user avatar
  • 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 ,...
Racer SQL's user avatar
  • 7,416
2 votes
1 answer
68 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 --------------------...
Johann B.'s user avatar
2 votes
2 answers
68 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). ----------...
Dilbert Vanwinklespout's user avatar
1 vote
0 answers
12 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 ...
Maissa Oueslati's user avatar
1 vote
0 answers
221 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) ...
Matteo Tassinari's user avatar
1 vote
0 answers
45 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 ...
Tiago Silva's user avatar
1 vote
1 answer
139 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/...
hanisharun's user avatar
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 ( ...
Ansar Mohiuddin Mohammed's user avatar
1 vote
0 answers
841 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 ...
Martin.'s user avatar
  • 111
1 vote
2 answers
50 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 ...
Hanno Hoogenboezem's user avatar
1 vote
1 answer
41 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....
Racer SQL's user avatar
  • 7,416
1 vote
0 answers
45 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 ...
Jim M's user avatar
  • 11
1 vote
0 answers
146 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. ...
flocmoi09's user avatar
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 #...
Dante Salvador's user avatar
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 ...
Talib's user avatar
  • 23
1 vote
0 answers
34 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 ...
user7385063's user avatar
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 ...
jamesmstone's user avatar
1 vote
1 answer
39 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 ...
Robin Koch's user avatar
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 ...
Praised's user avatar
  • 11
1 vote
0 answers
131 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 = ...
pppp's user avatar
  • 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 ...
Worker's user avatar
  • 519
1 vote
0 answers
778 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 ...
Petay87's user avatar
  • 113
1 vote
0 answers
786 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 ...
user721's user avatar
  • 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` ...
Gunjan.sh's user avatar
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 ...
Herbiek's user avatar
  • 35
0 votes
1 answer
42 views

SQL Pivot table splitting rows on custom column names. I need 1 row per OrderId

I have this PIVOT table below. I have to dynamically create column headings based on the itemid value (1 to 50) in each order. then horizontally list them. in this example, it works, but it splits ...
paygboy00's user avatar
0 votes
0 answers
29 views

How to do many to many query that returns dynamic number of columns?

I have 2 tables that have a many to many relationship. An items table and a properties table. They are connected by a junction table. Basic versions of the tables below. item_name sword helmet axe ...
Brian Hung's user avatar
0 votes
1 answer
84 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 ...
inD4MNL4T0R's user avatar
0 votes
0 answers
32 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 ...
epm.007's user avatar
  • 29
0 votes
1 answer
22 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 ...
ceving's user avatar
  • 339
0 votes
0 answers
192 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 ...
Kiquenet's user avatar
  • 145
0 votes
0 answers
65 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
Manish Bose's user avatar
0 votes
0 answers
669 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) ...
Kum's user avatar
  • 47
0 votes
0 answers
43 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 | +----+-----------+-------+...
Baz's user avatar
  • 165
0 votes
2 answers
3k 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 ...
maphy-psd's user avatar
0 votes
0 answers
65 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 ...
ahmed elbarbary's user avatar
0 votes
0 answers
312 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 (...
rafamaniac's user avatar
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 ...
Eliza's user avatar
  • 1
0 votes
0 answers
780 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 ...
ilhnctn's user avatar
  • 101
0 votes
0 answers
141 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 ...
NAS BS's user avatar
  • 1
0 votes
1 answer
183 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 ...
Christopher Martinez's user avatar
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 ...
user2050638's user avatar