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
0answers
28 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 ...
0
votes
1answer
174 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 ...
0
votes
1answer
23 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 ...
0
votes
1answer
30 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 ...
0
votes
1answer
31 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'...
0
votes
1answer
27 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 ...
0
votes
1answer
48 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 ...
0
votes
0answers
49 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 (...
0
votes
1answer
33 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 ...
0
votes
1answer
23 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 ...
1
vote
1answer
55 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/...
0
votes
2answers
40 views

SQL Pivot with multiple columns [duplicate]

I have a table as shown below +-----+------- ---+------------+------------+ | Id | Location | DateIn | Dateout | +-----+-----------+------------+------------+ | 117 | SHIP | 2021-09-01 | ...
1
vote
0answers
37 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 ...
0
votes
1answer
143 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'), ... ...
0
votes
0answers
160 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
vote
2answers
118 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 ...
0
votes
0answers
22 views

Foreign key on another foreign key && Pivot Table

I need to design a web app that function as poll system. It should have multiple polls with only 2 contestants which can be repeated in other polls too. For each contestant in the poll I need to count ...
1
vote
2answers
38 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 ------------- ------------------...
0
votes
1answer
66 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 ...
0
votes
0answers
62 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 ...
0
votes
0answers
90 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 -...
0
votes
0answers
88 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 ...
0
votes
1answer
40 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 ...
-4
votes
1answer
59 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 ...
0
votes
1answer
30 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 ...
0
votes
1answer
275 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 ...
0
votes
2answers
17 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 ...
3
votes
1answer
107 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 ...
0
votes
0answers
31 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 ...
0
votes
1answer
39 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
0answers
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 ...
2
votes
2answers
70 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.
0
votes
1answer
51 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
3answers
34 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 | ...
0
votes
1answer
33 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 ...
2
votes
1answer
226 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 ...
1
vote
2answers
259 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 ...
0
votes
0answers
30 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, ...
2
votes
2answers
83 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 ...
-1
votes
1answer
300 views

Write a dynamic query of nested subqueries [duplicate]

Let's take an everyday query with a list of nested queries so I can show you the problem, see how we'd have to add a subquery when a new Certification comes out: select DISTINCT e.display_name as &...
0
votes
2answers
870 views

MySQL - Get counts of `car models` by `car make`

I have a table with cars data, i.e. Makes, Models, & Year. I want to get the count for each Car with how many models it has... something like the image below: This is the SQL query I am using ...
-1
votes
1answer
191 views

Convert columns to rows without using pivot [duplicate]

I have been assigned to get the data in required format from two tables. TableStaff : STaffID | Staff Name -------------------- 1 | John 2 | Jack and TableLead LeadID | LeadValue | ...
0
votes
1answer
44 views

SQL Query - Pivot with Ranges

This is my sample table BSEG_ID DST_ID BILL_SQ AUDIT_CALC_AMT BILL_SQ, MSR_PEAK_QTY_SW, DESCR_ON_BILL, VERSION, AUDIT_CALC_AMT 12334 REV-WSC 62 6.40667 For 0 - 60 (Next 62.000 cubic ...
0
votes
2answers
19 views

Getting averages based on column values

Editing because I made a mistake transforming from my real example with lots of irrelevant details to the boxes example: I have a table BOXES like this: box_id | color | dimension | value 1 ...
2
votes
1answer
575 views

SQL: pivot non numeric data [duplicate]

I have a set of data which basically looks like the following: +---------+--------+ | EventId | Field | +---------+--------+ | 1 | apple | | 1 | paper | | 1 | boy | | 2 | ...
0
votes
0answers
56 views

Pivoting dynamic columns

I trying to pivot a table creating the columns dynamically according to the rows. I'm using the clause PIVOT XML, but I'm not getting parse the XML column to columns. My table looks like this: I ...
-2
votes
2answers
34 views

Turning columns result set into independant rows in MySQL

I am pretty new to this and have been struggling over one issue. I have a result set which for each Opportunity in a table (id as primary key), provides a date for a first cashflow (DFCFW) as a column ...
1
vote
2answers
29 views

Build a select query that includes aggregated values from a different table

I have two tables in Postgres: polls and votes. The first one polls is designed to contain the poll-related data. Every poll is aimed to have only two possible responses — option_a and option_b +----+-...
0
votes
1answer
528 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
0answers
70 views

SQL to display one row as many based on one column

Is it possible to display one row as multiple rows based on the values in one column ON THE FLY? Example... Data: column1 (text) | column 2 (text) ----------------------------------- one ...

1
2 3 4 5
9