1,416 questions
0
votes
0
answers
40
views
How to merge rows with the same ID into one row by taking non-null values
I am working with a table in DolphinDB where multiple rows share the same id, and each row contains non-null values in different columns. My goal is to consolidate these rows into a single row for ...
1
vote
1
answer
153
views
What's the difference between "Unpivot Columns" and "Unpivot Only Selected Columns" in Power Query?
When I select multiple rows and right click in Microsoft Power Query, (in Excel 2016), I see two similar options for unpivoting: "Unpivot Columns" and "Unpivot Only Selected Columns&...
- 27.8k
-3
votes
2
answers
122
views
Unpivoting/Pivoting Sales Data Around Customer/Store Data [closed]
My data is simple, but I need to pivot it for a tableau correlation table. Trying to pivot my sales data from one customer per row to products down through a single column along with their counts in ...
-1
votes
3
answers
100
views
Unpivot based on prefix
I want to compare two rows in a same table.
SELECT OLD.[Addressee] OLD_Addressee, NEW.[Addressee] NEW_Addressee,
OLD.[BuildingName] OLD_BuildingName, NEW.[BuildingName] NEW_BuildigName
FROM [dbo]....
- 447
0
votes
1
answer
37
views
Oracle sql UNPIVOT is keeping nulls I don't want
I'm using UNPIVOT to combine columns and the result is keeping some unwanted null rows.
I have data like shown below.
Part | Chart | Tool Part | Tool Chart
-----|-------|-----------|------------
401 |...
- 11
5
votes
5
answers
302
views
Expand Struct columns into rows in Polars
Say we have this dataframe:
import polars as pl
df = pl.DataFrame({'EU': {'size': 10, 'GDP': 80},
'US': {'size': 100, 'GDP': 800},
'AS': {'size': 80, 'GDP': 500}}...
- 3,611
3
votes
1
answer
54
views
Matlab: how to unstack a table and filter reslting matrix
I have a table that looks as follows:
and I would like to get it into this format:
in order to ultimately isolate all cells that have non-zero values:
I need to be able to identify row name and ...
0
votes
2
answers
117
views
Simplest way to convert aggregated data to visualize in polars
Suppose I have aggregated the mean and the median of some value over 3 months, like:
df = (data.group_by('month_code').agg(pl.col('value').mean().alias('avg'),
pd....
- 1,184
0
votes
1
answer
75
views
Unpivot with no-constant expression
I have data like this (notice that my month are in format 'yyyymm'):
car
month_1
amount_1
month_2
amount_2
A
202412
100
202501
50
B
202412
200
202501
20
C
202412
300
202501
900
I would like to unpivot ...
- 15
0
votes
0
answers
18
views
Oracle pivot a two column table / Oracle pivotar una table de dos columnas
I have the following table with values / tengo la siguiente tabla con valores
Col_concept | Col_description
URL https//XXX.X.X.XX:8080/
PASS Basic "#EDFRIORP$)$)...
1
vote
2
answers
119
views
Table function in SQL Server with multiple parameters as an argument
I have a table in SQL Server 2016 named df:
-- Create a new table with department and gender columns
CREATE TABLE df
(
country VARCHAR(50),
year INT,
val1 INT,
val2 INT,
val3 INT,
...
- 1,358
0
votes
2
answers
194
views
Polars faster alternative to successive joins
I have some big dataset and I need to do multiple successive joins that are slow. I figured an alternative was to unpivot the whole dataframe I was merging successfully, join once and then get the ...
- 45
-1
votes
1
answer
74
views
UNPIVOT from the output of Temp Local Table [closed]
I have the following SQL where I'm trying to UNPIVOT the result from the temp local table #tempTbl_tblF0902
SELECT
[JDE Fiscal Year], [Business Unit], [Cost Code], [Cost Description], [Cost with ...
- 117
1
vote
2
answers
83
views
Unpivot snowflake table with pivot column alias
I got the following example of how to unpivot Snowflake tables:
SELECT *
FROM monthly_sales
UNPIVOT (sales FOR month IN (jan, feb, mar, apr))
ORDER BY empid;
This is very nice, but I need the ...
- 5,515
2
votes
1
answer
231
views
polars unpivot columns then rows
During solving Excel BI challenge
I used Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"). And this gave me an output:
Next I was trying to solve the same challenge in ...
- 79
0
votes
1
answer
60
views
How to sort by number in a text.combine field for multiple columns?
I have a generated report where I basically 'transpose' and combine two separate columns (Unpivot, Remove Duplicates, Pivot, Text.Combine, List.Sort).
I found this post, where it shows how to sort a ...
- 111
1
vote
4
answers
126
views
Can I use PIVOT or UNPIVOT to get the desired result and eliminate a slew of UNIONs?
I know I could easily use UNION ALL to get the results I want, but I have to generate 5 tables of each containing 15 rows of metrics/KPI.
To simplify, I have data like this:
user
sex
state
race
usr1
M
...
- 901
0
votes
0
answers
50
views
Converting multiple columns to rows using UNPIVOT
I am a beginner SQL user. I wrote a query to extract several Fuel emissions columns and wrote a display message to show if it meets the requirements (Previous emission - Fuel Emission = Fuel ...
- 1
3
votes
2
answers
2k
views
DuckDB - Unnest map that have different keys
EDIT: as a new duckdb user, I misinterpretted the output syntax as a struct it turns out I had created a map
I have a table that contains a map
Product
Sales
Widget
{Jan=12, Feb=13}
Dongle
{Feb=1, Apr=...
- 3,019
0
votes
1
answer
131
views
Optimizing pivot/unpivot & transform queries SQL
I need advise how to optimize my query / my pivot/unpivot actions as it's taking a lot of time for this procedure to run.
Basically I have data with a lot of values in columns. Value data is split by ...
- 71
-1
votes
2
answers
84
views
How to get for each non blank data entry in a range output in three columns containing the entry itself , the column header and the row header [duplicate]
I start with a range of data (a formula for instance combining the column/row header and/or "text" and or empty items).
the first row above the range is a column header (number or letter or ...
- 45
3
votes
2
answers
289
views
Handling Multiple Operations on DataFrame Columns with Polars
I'm trying to select all columns of a DataFrame and perform multiple operations on each column using Polars. For example, I discovered that I can use the following code to count non-null values in ...
- 1,209
3
votes
2
answers
269
views
Expand/Unnest Polars struct into rows, not into columns
I have this DataFrame
import polars as pl
df = pl.DataFrame({
'as_of': ['2024-08-01', '2024-08-02', '2024-08-03', '2024-08-04'],
'quantity': [{'A': 10, 'B': 5}, {'A': 11, 'B': 7}, {'A': 9, ...
- 3,611
0
votes
2
answers
55
views
How to UNPIVOT into multiple columns using unpivot or union all? [duplicate]
I have a table like this.
Name Status 1 Action 1 Status 2 Action 2 Status 3 Action 3
AAA Started S Pended P Closed C
BBB Started ...
0
votes
0
answers
27
views
I have a table which has 3 diff status column like status1,status2 and status 3 and for each status I want a separate row [duplicate]
I have a table which has 3 ref no and 3 diff status column like started,pended and closed and for each status I want a separate row. Addition to that in output we shall have a sequence no column for ...
0
votes
0
answers
44
views
unpivot and pivot conversion from sql server to postgres
I am trying to perforrm conversion from sqlserver script to postgres
postgres not supporting format,unpivot and pivot function is there any alternate method to convert these to postgres
select [values]...
- 1
0
votes
1
answer
94
views
How to unpivot a Pandas dataframe on two ID fields with multiple sets of columns
I have a pandas Dataframe that looks like this:
pid
fid
FirstReasonName
FirstReasonValue
SecondReasonName
SecondReasonValue
1
'x'
'a'
3
'b'
8.2
2
'y'
'c'
8
'd'
7
Now I want to unpivot it on pid and ...
- 5,049
-1
votes
1
answer
65
views
Converting multiple columns into rows [duplicate]
I have an input table in Oracle like this. How do i get an output by converting the columns (year number) and corresponding values into rows
INPUT_TABLE
KEY_ID YR1 YR2 ...
- 1
2
votes
1
answer
192
views
Efficient method for adding derived data to a Polars multiIndex LazyFrame
I am working with Polars and need to add derived data to a multiindex LazyFrame.
To modify the data, I am pivoting the DataFrame without performing any aggregations, doing some computations, and then ...
- 423
0
votes
2
answers
60
views
Convert multiple rows to column (unpivot)
I am attempting to convert table1 to the output shown in table two. Is there a way to use multiple unpivot to accomplish this without skewing the data. Not sure what I'm missing but I'm getting 4 ...
1
vote
1
answer
118
views
Unpivot with Pandas - Excel a specific type of matrix?
I am using Python/Pandas to extract data from an excel file and do some processes but I am stuck dealing with this:
I have this data (is not formatted as a table) in my data frame:
column1
column2
...
0
votes
1
answer
141
views
Slow MySQL query with multiple sub-queries in where clause
I have a MySQL statement that lists a series of products, that belong to different sets, and orders the results as one
product for each set.
My products are organized like this.
Set 01
Set 02
Set 03
...
- 7,507
0
votes
1
answer
82
views
Unpivot Dataframe with multilayered headers into long format
I have an excel file which has a pivoted table. I want to bring it into a long format so the same can be inserted into a DB.
loading the file in df.
file_path = '/content/drive/My Drive/...
1
vote
1
answer
532
views
Transpose columns to rows - multiple columns
I have a query output that is formatted like this:
yr_mo
Sales
Transactions
Items
Shipping Cost
Tax
2024-04
20
50
100
12
2
2024-03
10
25
50
6
1
This isn't a great way to view the data, since the data ...
- 11
3
votes
2
answers
116
views
Using Power Query to unpivot table
Original
ID
Type 1
Count 1
Type 2
Count 2
Type 3
Count 3
1
a
640
d
Type 3
a
2
d
12000
a
1900
f
6000
3
f
48000
f
3600
e
1600
4
c
46000
e
3100
b
1200
5
e
47000
c
3400
d
1400
6
b
64000
b
3600
c
1200
I ...
0
votes
1
answer
136
views
SQL - Stack multiple substrings in one column
I am looking to stack multiple substrings in one column. I am looking for alternatives to UNION ALL because my real data is massive and there are over 30 occurrences of sub strings for each row.
Below ...
- 1
0
votes
2
answers
35
views
unpivot multiple columns in Oracle
I have such table with 5 columns
order code1 value1 code2 value2
I want to to have one column for code and one column for value.
I can use union all
select order,code1,value1 from table
union all
...
- 333
-1
votes
1
answer
26
views
Power Bi, colums to rows
I have this table :
Date column contains each market date, there are different oil price columns, some columns to capture change in price from previous day, and some column capturing change in %.
I ...
4
votes
1
answer
2k
views
Pivot a Polars dataframe (like pivot_longer in R)
Coming from R I am remaking some exercises that helped me a lot. So Trying to recreate this R code:
wide_data <- read_csv('https://raw.githubusercontent.com/rafalab/dslabs/master/inst/extdata/life-...
- 91
1
vote
4
answers
115
views
SQL Query Group Like Values within the same row and concatenate and sort others
Using SQL Version 15.0.2000.5
I have a table with this data:
StudentScheduleId
Monday
Tuesday
Wednesday
Thursday
Friday
MondayStartTime
MondayEndTime
TuesdayStartTime
TuesdayEndTime
WednesdayStartTime
...
- 2,977
-2
votes
2
answers
161
views
Google Sheets : Need help in automating the transpose option from columns to rows [duplicate]
I am looking for an option in Google sheets where I can automate pivot/ transpose the values in columns to rows. something similar to power pivot in excel.
I have shared a sample and my requirement in ...
- 3
1
vote
1
answer
73
views
SQL Unpivot with Additional variable field creation
I have created the following query:
SELECT UnPivotTable.*
FROM ( SELECT
"Stage 1" as "Stage 1 Date",
"Stage 2" as "Stage 2 Date",
...
1
vote
1
answer
88
views
how to gather categorized monthly sales in ms access form and retrive them as new record per month-product-category
I have designed a sales budgeting system using "MS Access" in which users can assign sales Value in addition of sales price for different months along with some other functionality.
Here is ...
3
votes
1
answer
277
views
Same result without using UNION ALL on repeating SELECT statements?
I built a table in Amazon QuickSight, but the way the viewer wants the data organized in a pivot table, I'm only able to achieve with the below SQL snippet. I don't have a problem leaving it as is, ...
- 194
5
votes
3
answers
835
views
merge groups of columns in a polars dataframe to single columns
I have a polars dataframe with columns a_0, a_1, a_2, b_0, b_1, b_2. I want to convert it to a longer and thinner dataframe (3 x rows, but just 2 columns a and b), so that a contains a_0[0], a_1[0], ...
- 5,738
0
votes
2
answers
383
views
How can I transpose some specific columns, while repeating first 4 columns using a formula in Google Sheets? [duplicate]
I would need the first 4 columns to have their data repeated, while the others are stacked up, like the example below and whose link can be found here:
- 2,386
1
vote
1
answer
1k
views
Polars pivot + unpivot operation with multiple values (pandas stack / unstack alternative / UDF over)
I am trying to pivot a table on 2 value columns, apply some user defined function on the pivotted dataframe and then unpivot (stack in pandas). In pandas, this would look like this:
import pandas as ...
- 45
0
votes
2
answers
332
views
Unpivot columns using CROSS APPLY where values include int and varchar
I am attempting to unpivot a table column but am encountering a conversion error from varchar to int. All of the columns in my table are ints except one, which is varchar. The error is detailed below.
...
- 47
0
votes
2
answers
95
views
Taking a wide, one-row query and making it a one-column result
I have a query that is several hundred columns wide with a single row. I want to manipulate the data in Excel, and having a tall, narrow result is my goal. Every column is a data value, and I'm not ...
- 55
0
votes
1
answer
81
views
Unpivoting data in google sheet
I have the following data in Excel:
How do I unpivot the above into the following in Excel or Google Sheets?
- 183