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.
478 questions
0
votes
1
answer
68
views
how to get the location of the backup too? you can use multiple columns pivoting or any other way!
I got this great query that shows the latest backup for each existing database.
SELECT
M.name,
[Recovery Model] =
M.recovery_model_desc,
[State] =
M.state_desc,
[Last ...
- 16.6k
0
votes
1
answer
57
views
T-SQL how to dynamically Pivot a tree
I've got a simple parent-child tree relationship that was built recursively
Parent
Child
A
B
B
E
B
C
E
NULL
C
NULL
A
F
F
NULL
this table comes from a recursive CTE:
with tree(parent, child) ...
- 117
-1
votes
2
answers
90
views
Sql query result in a single row with multiple dynamic columns
i have multiple rows and want to make dynamic column i tried below code with case it work fine but i need the column to be dynamic based on the count of max Code
EventId
Code
Date
1
A12
12/12/2020
...
- 1
1
vote
1
answer
51
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 * ...
- 21
0
votes
1
answer
80
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
0
answers
36
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
...
- 17
-2
votes
2
answers
88
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
...
1
vote
0
answers
17
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
584
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' ...
- 113
0
votes
2
answers
577
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
50
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
98
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
0
answers
34
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 ...
- 29
0
votes
1
answer
24
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 ...
- 359
0
votes
0
answers
220
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 ...
- 155
-1
votes
1
answer
144
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
----------- --...
- 51
3
votes
0
answers
128
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 ...
- 31
2
votes
1
answer
403
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 ...
- 23
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
257
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) ...
- 167
0
votes
1
answer
726
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?
- 65
-1
votes
1
answer
32
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-...
- 99
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-...
- 33
0
votes
1
answer
176
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
436
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,
...
- 11
3
votes
0
answers
76
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:
+----+------+-----+-----+-----+-----+-----+-----+-----+-----+-----+----...
- 131
0
votes
0
answers
77
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
9k
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,...
- 135
2
votes
1
answer
390
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 |...
0
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
...
- 143
7
votes
1
answer
740
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 ...
- 177
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
...
- 31
-1
votes
1
answer
777
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 ...
- 25
1
vote
1
answer
2k
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 ...
- 25
0
votes
0
answers
729
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) ...
- 47
0
votes
1
answer
49
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 ...
- 11
1
vote
1
answer
686
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",
...
- 25
0
votes
0
answers
48
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 |
+----+-----------+-------+...
- 165
0
votes
1
answer
77
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(...
1
vote
1
answer
528
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 ...
- 41
-1
votes
1
answer
111
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
...
1
vote
1
answer
6k
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 ...
- 33
1
vote
1
answer
300
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.
- 11
-4
votes
1
answer
45
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?
( ...
- 1
0
votes
1
answer
80
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:
+------...
- 101
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 ...
0
votes
1
answer
195
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 ...
6
votes
2
answers
27k
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
votes
2
answers
426
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 ...
