Stack Overflow
Stack Overflow

Questions tagged [unpivot]

UNPIVOT describes a Query Language feature of some Relational Databases. The UNPIVOT feature converts columnar data into row data, and vice versa for the PIVOT feature.

Overview

UNPIVOT describes a Query Language feature of some Relational Databases. The UNPIVOT feature converts columnar data into row data, and vice versa for the PIVOT feature.

Databases that support PIVOT/UNPIVOT syntax:

  • Oracle 11g+
  • SQL Server 2005+

Alternative approaches

While MySQL does not have a UNPIVOT function, this can be replicated using a SELECT statement with UNION ALL for each column.

Alternative meanings

UNPIVOT is also a feature of other applications such as Business Intelligence Query tools and some spreadsheets.

1361 questions
3
votes
1 answer

Unpivoting Data in SSIS

I am attempting to normalize data using SSIS in the following format: SerialNumber Date R01 R02 R03 R04 ------------------------------------------- 1 9/25/2011 9 6 1 2 1 9/26/2011 4 1 3 5 2 …
Brian Swart
  • 922
  • 1
  • 9
  • 25
3
votes
2 answers

Unpivot pairs of associated columns to rows

I have a table with staggered article prices like this: ArtNr Amount1 Price1 Amount2 Price2 Amount3 Price3 -------------------------------------------------------------- 4711 1 3.5 5 3.0 10 2.5 4712…
Prefect73
  • 321
  • 3
  • 14
3
votes
2 answers

how to un-pivot multi-column data?

I am trying to Unpivot columns and get 1 attributes and 2 value from a pandas dataframe, could anyone help me on this? original data: id Jan-Value1 Jan-Value2 Feb-Value1 Feb-Value2 1 1 10 2 15 2 0 5 3…
rain
  • 75
  • 4
3
votes
1 answer

Replace Union All with Or Sql Server

I have below table with some sample data Create table dbo.Test_2020 ( id int identity(1,1), level_cd_1 varchar(10) null, level_cd_2 varchar(10) null ) insert into dbo.Test_2020 select 'cd_1_01',null union all select 'cd_1_02',null union…
user1941025
  • 541
  • 6
  • 21
3
votes
2 answers

How to unpivot a large spark dataframe?

I have seen a few solutions to unpivot a spark dataframe when the number of columns is reasonably low and that the columns' names can be hardcoded. Do you have a scalable solution to unpivot a dataframe with numerous columns? Below is a toy…
mobupu
  • 245
  • 3
  • 10
3
votes
1 answer

Pivot and unpivot a table with multiple columns and Rows in SQL

I need to output this below table as following. Below is existing database table output. UNIQUE_ID PARTICULARS 18-Jan 18-Feb 18-Mar ----- ----- ----- ----- ----- 1 Direct…
lochana
  • 121
  • 3
  • 15
3
votes
2 answers

Pandas melt on MultiIndex columns

I have a csv file in the following format: | a | b | 2018 | 2018 | 2019 | 2019 | | | | jan | feb | jan | feb | --------------------------------------- | a1 | b1 | 0 | 1 | 2 | 3 | | a1 | b2 | 4 | 5 | 6 | 7 | | a2 |…
KOB
  • 4,084
  • 9
  • 44
  • 88
3
votes
0 answers

How to filter IN and OUT in day and night shift attendance?

I am developing an timekeeping system which has a day and a night shift schedule. Here is my table [tbl_datetime_records] +---------------+---------------+--------------------+ | biometrics_id | employee_name | date_time_logs …
Clintuy
  • 69
  • 8
3
votes
2 answers

How to make a progressive in SQL that increments each x records where x can potentially be different for each row?

I'm trying to write a progressive number in my SQL code that increments every x record, but this x can vary each time. The result that I want looks like this. N1 Var Date 1 x1 Date1 1 x2 Date1 1 x3 Date1 2 x1 Date2 2 x3 Date2 3 x2 …
dimerazor
  • 41
  • 5
3
votes
1 answer

Transpose single row with multiple columns into multiple rows of two columns

I have a SELECT query that works perfectly fine and it returns a single row with multiple named columns: | registered | downloaded | subscribed | requested_invoice | paid | |------------|------------|------------|-------------------|------| | 9000 …
prm
  • 120
  • 1
  • 8
3
votes
1 answer

How to reshape data for wide to long form by monthly columns name GoogleSheet

In R , data.table library dcast() can transform dataset from wide to long shape ,how can i do this in googlespreadsheet? From this format : | JAN | FEB | MAR ----------+------------+------------+----------- Product 1 | …
rane
  • 901
  • 4
  • 12
  • 24
3
votes
2 answers

How to ignore string value using unpivot function finding the min value among other columns

I have a table looks like this The goal is to find a min value among columns Limit (Limit1, Limit2,Limit3...etc). For that, I am using UNPIVOT function. But the problem is that some values are empty strings. As a result the min value becomes…
Serdia
  • 4,242
  • 22
  • 86
  • 159
3
votes
1 answer

Multiple column pivot

I need help with multiple column aggregate using pivot in mssql. Below is the temporary table for class assessments. This table contain list of class assessment which include: assessment code date of assessment total item passing…
Dante Salvador
  • 43
  • 4
3
votes
2 answers

SQL Dynamic Pivoting a "Week" Table

Table design: | PeriodStart | Person | Day1 | Day2 | Day3 | Day4 | Day5 | Day6 | Day7 | ------------------------------------------------------------------------- | 01/01/2018 | 123 | 2 | 4 | 6 | 8 | 10 | 12 | 14 | | 01/15/2018 …
wh4tshisf4c3
  • 133
  • 12
3
votes
2 answers

Expand Start Date to End Date with Series of EOMONTHs

I have a data table containing ids with a start date and end date associated with both. RowNo AcNo StartDate EndDate 1 R125 01/10/2017 30/09/2020 2 R126 01/10/2017 30/09/2018 3 R127 01/10/2017 …
user4039065
1 2 3
…
90 91