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
0 answers
24 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
-2 votes
2 answers
64 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 ...
charu gupta's user avatar
1 vote
0 answers
8 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
1 answer
79 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' ...
Aaron's user avatar
  • 113
0 votes
2 answers
87 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....
SpiderMan's user avatar
-1 votes
1 answer
39 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 ...
Cuauhtemoc's user avatar
0 votes
1 answer
61 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
31 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
21 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
  • 275
0 votes
0 answers
135 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
-1 votes
1 answer
90 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 ----------- --...
AKedzierski's user avatar
3 votes
0 answers
75 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
2 votes
1 answer
329 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 ...
jenny's user avatar
  • 23
0 votes
0 answers
55 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 ...
Shahrul Amir's user avatar
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 ...
RkassaTeam Breda's user avatar
1 vote
0 answers
157 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
0 votes
1 answer
490 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?
Jimbo's user avatar
  • 65
-1 votes
1 answer
26 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-...
santosh's user avatar
  • 99
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-...
xentwo's user avatar
  • 33
0 votes
1 answer
169 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 ...
Muhammad ammar usmani's user avatar
0 votes
1 answer
244 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, ...
broken_cursor's user avatar
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, ...
Guest's user avatar
  • 11
3 votes
0 answers
59 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
0 votes
0 answers
62 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
1 answer
6k 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,...
Dmitry Sazhnev's user avatar
2 votes
1 answer
282 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 |...
NorthernMonkey's user avatar
-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 ...
Applez00800's user avatar
7 votes
1 answer
614 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 ...
arilwan's user avatar
  • 177
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 ...
dbase964's user avatar
-1 votes
1 answer
629 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 ...
NasahT's user avatar
  • 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 ...
NasahT's user avatar
  • 25
0 votes
0 answers
591 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
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 ...
Raf's user avatar
  • 11
1 vote
1 answer
439 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", ...
NasahT's user avatar
  • 25
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
1 answer
62 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(...
Danny Nahas's user avatar
1 vote
1 answer
396 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 ...
Lespiegle's user avatar
-1 votes
1 answer
66 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 ...
user1553584's user avatar
1 vote
1 answer
5k 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 ...
w123's user avatar
  • 33
1 vote
1 answer
265 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.
RISHAB GUPTA's user avatar
-4 votes
1 answer
44 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? ( ...
Detox's user avatar
  • 1
0 votes
1 answer
74 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: +------...
ahmed barbary's user avatar
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 ...
maphy-psd's user avatar
0 votes
1 answer
171 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 ...
Rami Ouanes's user avatar
5 votes
2 answers
23k 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 ...
Brylie Christopher Oxley's user avatar
-1 votes
2 answers
382 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 ...
Gregory Cmar's user avatar
3 votes
2 answers
6k 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 ...
Mondi Koci's user avatar
0 votes
0 answers
63 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
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 ...
Reshmi Nair's user avatar
0 votes
1 answer
619 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 ...
Akshay Aravindan's user avatar

1
2 3 4 5
10