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.

Filter by
Sorted by
Tagged with
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 ...
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....
0 votes
1 answer
890 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
1 answer
29 views

PostgreSQL crosstab query not using view owner permission when queried from other users

I'd like some help with regards to creating a view in PostgreSQL using the crosstab function. I've been trying to create a view as follows: create or replace view csv_data as with answers as( select * ...
0 votes
1 answer
2k views

Oracle convert multiple rows to column using pivot

I have a table that contains the values +-------------+--------------+---------------------+-------------+-------------+--------------+-------+---------+-----------+---------------+--------------+ |...
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 ...
0 votes
1 answer
817 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 ...
0 votes
1 answer
41 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 ...
0 votes
1 answer
318 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 ...
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 ( ...
0 votes
1 answer
288 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 ...
0 votes
1 answer
3k 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 ...
0 votes
1 answer
32 views

Pivot table of student location information

I have a table that records the names of students and the continent they are from. For example: name continent Michael America Steve Europe Jo Africa Paul Europe Mike Africa Frankie Africa Now ...
-2 votes
2 answers
76 views

Insert data from one table to another

I have a table in which new entries come in every second. The table is just like this: value1 data1 Value2 data2 1 Er 2 Er 3 Er 4 Er Now insert these value in other table as Value data 1 Er 2 ...
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 ...
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 ...
1 vote
1 answer
255 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' ...
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 ...
0 votes
2 answers
315 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....
-1 votes
1 answer
48 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
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 ...
0 votes
1 answer
315 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 |...
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 ...
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 ...
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 ...
-1 votes
1 answer
121 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 ----------- --...
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 ...
2 votes
1 answer
382 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 ...
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 ...
5 votes
2 answers
26k 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
29 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
220 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) ...
0 votes
1 answer
627 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?
-1 votes
1 answer
30 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-...
7 votes
3 answers
10k 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
2k 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-...
10 votes
4 answers
8k 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 ...
0 votes
1 answer
170 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
349 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, ...
0 votes
1 answer
256 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 ...
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: +----+------+-----+-----+-----+-----+-----+-----+-----+-----+-----+----...
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
0 votes
1 answer
8k 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,...
0 votes
1 answer
188 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 ...
1 vote
1 answer
4k 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/...
2 votes
1 answer
344 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 |...
-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 ...
7 votes
1 answer
686 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 ...
0 votes
1 answer
186 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 ...

1
2 3 4 5
10