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
1 vote
1 answer
17 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.
user avatar
-4 votes
1 answer
41 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? ( ...
user avatar
  • 1
0 votes
1 answer
55 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: +------...
user avatar
0 votes
1 answer
46 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 ...
user avatar
0 votes
0 answers
30 views

Indexing a pivot table. Is really necessary?

I'm creating a small app for a contractor where an Event is assisted by multiple Clients. ┌─────────┐ ┌───────────┐ ┌────────┐ │ Event ◄──────► Assitance ◄──────► Client │ └─────────┘ ...
user avatar
0 votes
0 answers
42 views

How to create correct mysql update query using window function for grouped items

We have a table that you could label as a summary table that we need to update every so often from another table that contains dated sales rows. How do you create a query, along with the index for it, ...
user avatar
  • 146
0 votes
0 answers
17 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 ...
user avatar
3 votes
1 answer
424 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 ...
user avatar
-1 votes
2 answers
76 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 ...
user avatar
0 votes
0 answers
29 views

Optimizing a view query that uses pivot aggregation - Oracle

I have a table with columns as - | ID | cat_1 | cat_2 | type | value | |----|-------|-------|------|-------| |1 | a |b | t1 | 4 | |2 | b |d | t2 | 5 | |3 | b |e ...
user avatar
3 votes
2 answers
340 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 ...
user avatar
0 votes
0 answers
13 views

Query to Include All Records

I am trying to create an excel report and am running into an issue that I am most likely overthinking. What I am trying to do is create multiple pivot tables to show me my overall inventory and then a ...
user avatar
  • 126
0 votes
0 answers
35 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 ...
user avatar
2 votes
1 answer
994 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 ...
user avatar
0 votes
1 answer
49 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 ...
user avatar
0 votes
1 answer
36 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 ...
user avatar
0 votes
1 answer
51 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'...
user avatar
0 votes
1 answer
31 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 ...
user avatar
  • 115
0 votes
1 answer
624 views

How to set the column names dynamically in crosstab?

I have a table with key and value and I have saved the values as shown below when I use the below query select * from crosstab ( 'select rowname,colname,max(cellval) from table_to_pivot group by ...
user avatar
0 votes
0 answers
96 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 (...
user avatar
0 votes
1 answer
140 views

How to transpose a single key-value table in Postgres?

I have. Key-Value table of two columns. I want to get keys as column names and values in individual columns (named as were keys named before). How to do such thing in Postgres ? Is it possible to get ...
user avatar
  • 65
0 votes
1 answer
163 views

SQLite Aggregation then Transpose

I am currently uploading my solar inverter data to PVOutput using a Linux machine and a scratch built Perl script. I want to add the meter data from my enrgy supplier. I have an 87,000 row CSV file ...
user avatar
1 vote
1 answer
97 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/...
user avatar
0 votes
2 answers
171 views

SQL Pivot with multiple columns [duplicate]

I have a table as shown below +-----+------- ---+------------+------------+ | Id | Location | DateIn | Dateout | +-----+-----------+------------+------------+ | 117 | SHIP | 2021-09-01 | ...
user avatar
  • 101
1 vote
0 answers
38 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 ...
user avatar
0 votes
1 answer
478 views

In SQL Server, is there a way to pivot on an unknown number columns WITHOUT using dynamic SQL?

Essentially if we have this: create table #temp ([key] nchar, [value] nvarchar(2)); insert #temp ([key], [value]) values ('a', '1'), ('b', '2'), ... ...
user avatar
0 votes
0 answers
360 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 ...
user avatar
  • 1
1 vote
2 answers
188 views

Can I bring two values from a single row into a Pivot Table

I am trying to get two values from rows in a table into a single row in a pivot table. myTable has the following rows ID1 ID2 Item Value SP 11 22 Item 1 10 10 11 22 Item 2 100 100 11 22 Item 3 49 ...
user avatar
1 vote
2 answers
41 views

Pivoting table in MySQL

I was wondering if it is possible to change the output of this: User Database Select Insert Update Delete Create References Alter Drop ------------- ------------------...
user avatar
0 votes
1 answer
106 views

Combining multiple SELECT/WHERE into a result with multiple columns with alias names

I have a Timescale DB / PostgreSQL table (DataTable) which looks like this I'm trying to find a query which would return different columns for different subsystem/metrics combination like this ...
user avatar
0 votes
0 answers
78 views

Get all details by date without aggregate functions with dynamic pivot

I have the following table and I'm trying to put the [Fecha de pago] rows as a column, and I found similar questions but they are using aggregate functions like: Dynamic pivot: sum of sales per month ...
user avatar
0 votes
0 answers
216 views

CROSSTAB / cannot extract elements from a scalar HELP PLEASE!

I'm kind of lost here and I really need some wisdom from a Data Expert: cf.item ->> 'name' AS "custom_field" they want everything as a column and not create them as a row and cf.item -...
user avatar
0 votes
0 answers
220 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 ...
user avatar
  • 101
0 votes
1 answer
79 views

Pivot email rows into multiple columns per id

I have a table "emails": id email 1 a@a.a 2 g@g.g 2 d@d.d 4 c@c.c 2 k@k.k 4 o@o.o I need this result: id email email email 1 a@a.a 2 g@g.g d@d.d k@k.k 4 c@c.c o@o.o the number ...
user avatar
  • 3
-4 votes
1 answer
170 views

Horizontal to vertical table

I have this query SELECT quiz_id, COUNT(case when options LIKE '%question_id_8":"1%' then 1 else null end) as 'answered 1', COUNT(case when options LIKE '%question_id_8":"2%' then ...
user avatar
0 votes
1 answer
33 views

Pivoting a two-column table to show if a permutation exists

Struggling with the terminology here... I have a table of elements which may or may not be present in a group: elem | grp ---------- 1 | A 1 | B 2 | A 3 | C I'd like to turn this into a ...
user avatar
0 votes
1 answer
608 views

SQL Server PIVOT returns only one row [duplicate]

I have a simple table of of 2 columns(varchar): name |occupation ___________________ Sandra |Medic Julija |Actor Marija |Actor Martyna |Manager Aiste |Teacher Kristina|Teacher Virg |Teacher ...
user avatar
0 votes
2 answers
18 views

Select All Field (if field not exist, make field with null value)

I have a table with this field and condition table cc_queue_detail ticket_id field_name_id data_content 1 category_ticket_1 33 1 category_ticket_2 ...
user avatar
3 votes
1 answer
152 views

Properly pivot an XML column with XQuery

I would like to pivot the attributes and values of an SQL XML column and display it in an ordered columns and rows format. To get started, this is what the XML column looks like in SQL: <Model ...
user avatar
0 votes
0 answers
52 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 ...
user avatar
  • 1
0 votes
1 answer
59 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 ...
user avatar
0 votes
0 answers
20 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 ...
user avatar
2 votes
2 answers
75 views

MSQL - Switching rows to columns

I've been stuck on the following for quite a while, it seems like it should be relatively simple but I can't quite get my head round it.
user avatar
0 votes
1 answer
134 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 ...
user avatar
1 vote
3 answers
36 views

Filter table by multiple pivot values

I have the following tables: fabrics | id| title | code| |-- |---------| ----| | 1 | Fabric 1| FBR1| | 2 | Fabric 2| FBR2| | 3 | Fabric 3| FBR3| | 4 | Fabric 4| FBR4| attributes | id| title | ...
user avatar
  • 121
0 votes
2 answers
55 views

Pivoting 4 by 44 result set

I have a SQL Server query that gets the sales data for a 44 store market chain, it includes 4 values for each store (Sales, COGS, GP and GP Margin), I declared a CTE and joined it 44 times for each ...
user avatar
  • 126
2 votes
1 answer
291 views

How to PIVOT data from MSSQL query and Fetch to PHP view

I have a MSSQL 2005 Database with query content like this : nopas code name order 00115301 D031 ADAM 1 00130015 D031 ADAM 2 00149998 D026 JAMES 1 00149970 D028 FIKI 1 00142641 D028 FIKI 2 ...
user avatar
  • 21
1 vote
2 answers
381 views

Dynamic pivot table filtering and performance

I have these tables and I'm trying to rotate the subscriber to column table to horizontal and filter its result based on multiple and/or conditions like the following: WHERE first_name LIKE 'm%' AND ...
user avatar
  • 13
0 votes
0 answers
40 views

Creating table/columns combinations using Mysql Query

I have an existing scheme for products variations. I want to create a combination of each production time, quantities and variation options. I will create a selection form by accessing the quantities, ...
user avatar
2 votes
2 answers
86 views

Oracle Procedure alter pivoted data to unpivot and push into different destination

I have a PL/SQL procedure which takes string at runtime which is comma separated,splits it and pushes into table. Example of procedure and table is given here: Procedure is push_data. Current table ...
user avatar
  • 101

1
2 3 4 5
9