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
2
votes
2 answers

Convert column to multiple rows using SQL Server

This is my table structure: CREATE TABLE StudesntMarkList ( StudentID int, StudentName varchar(100), Performance varchar(100), class varchar(100), Section varchar(100), subject1 varchar(100), subjectmark1…
lingesh waran
  • 33
  • 4
2
votes
1 answer

Dynamic SQL to Unpivot Data using Cross Apply with Multiple Columns

Microsoft SQL Server Management Studio v18.8 I have a table that will have varying columns and column names. I need to unpivot the data so I can then eventually store it into a different table. Unfortunately, this is the process since the original…
MonkeyMonkey
  • 150
  • 1
  • 1
  • 13
2
votes
4 answers

Snowflake how can we run an unpivot query over an array of fields instead of explicitly declare each field?

I need to unpivot a table using fields specified in an array. I have the following query: select * FROM TEMP_TABLE_NAME T unpivot ( val_col for name_col in ( array_of_fields ) ); The issue is that our data is…
alim1990
  • 4,656
  • 12
  • 67
  • 130
2
votes
0 answers

Python Pandas: Unstack nested Excel Pivot report in compact form to get machine readable data

Hey guys I'm trying to turn this legacy data from an excel file into a machine readable form for further use. Data looks like this (This is the result of an excel pivot in compact form, which was copy pasted and the original data source is…
Lucas
  • 21
  • 1
2
votes
4 answers

VBA function to copy into new rows depending on the colum values

I`m not a super experienced VBA developer and mostly relly on the Macro recorder, hence would appreciate any help by the community in helping me wrap my head around this problem. I havent used loops in the past but imagine this would be the best…
Daniel
  • 21
  • 2
2
votes
2 answers

How do write an SQL query that transforms phone numbers from columns into a single column?

How do I write an SQL query that transforms phone numbers from columns into a single column. Assume there are multiple personIDs and each personID has up to 3 phone types, Primary, Secondary and Tertiary. Currently, for each person, they are listed…
cmomah
  • 165
  • 2
  • 9
2
votes
1 answer

Un-Pivoting Postgres for a large number of columns

So I've been looking at crosstab for pivoting but not sure if there is a more scalable way to do this. Right now I have a structure that looks like Date Amount1 Amount2 Amount3 Date 1 2 1 Date 1 3 2 Date 2 4 1 Date 3 5 2 I'd like…
Nicholas Tong
  • 105
  • 8
2
votes
2 answers

UNPIVOT Holiday Hours

I have a table, that keeps track of store holiday hours: LOCATION_ID DATE1 TIMES1 DATE2 TIMES2 123456 2020-12-12 10:00AM-09:00PM 2020-12-19 10:00AM-09:00PM This is a highly oversimplified table. There's about 30 columns…
Depth of Field
  • 307
  • 2
  • 16
2
votes
2 answers

Postgresql query to transpose columns to rows

I have a table like this one: id_edifc classe_a classe_b classe_c 9001 0 0 1 9002 1 1 1 9003 0 1 2 9004 0 0 0 9005 1 1 0 and I would like to visualize it like this instead: id_edifc classe 9001 classe_c 9002 classe_a 9002 classe_b 9002…
ishanka ganepola
  • 315
  • 1
  • 3
  • 6
2
votes
1 answer

Need to create a view in Impala which Un-Pivots, Pivots and Union's it altogether

I'm doing my first foray into something proper in SQL, except having trouble replicating something which I've been easily able to do via Alteryx. Essentially, some basic data wrangling needed to create a summary of a Table which I've already created…
ShushKebab
  • 35
  • 4
2
votes
2 answers

Insert a tableA into another tableB with a different structure

I have a table A looks like : id isin typ1 typ2 1 aa typA typB 2 bb …
Giovanni
  • 61
  • 6
2
votes
1 answer

TSQL PIVOT/UNPIVOT Multiple Summarized Columns

Seems this is a popular topic but I can't seem to replicate other answers. I have data that is already summarized by month/year for multiple categories. month calls deals productssold avgsaleprice 1 25 6 7 …
jdids
  • 561
  • 1
  • 7
  • 22
2
votes
1 answer

Dividing weekly salary info into daily

I'm trying to take salary data from our financial system and break it down into daily 'costs' so that it lines up with our hourly staff for better data analytics. Right now, salary data looks (something) like this: TRXBEGDT trxenddt StoreID…
SaintFrag
  • 127
  • 1
  • 13
2
votes
3 answers

Reversing Group By in PySpark

I am not sure about the correctness of the question itself. The solutions I've found for SQL do not work at Hive SQL or recursion is prohibited. Thus, I'd like to solve the problem in Pyspark and need a solution or at least ideas, how to tackle the…
xmatadorx
  • 35
  • 4
2
votes
2 answers

Convert a set of columns into rows in ORACLE SQL

I have a query that returns a table that looks somewhat like below. In the image, I want the Input table to look like the Output table -- Essentially, I want all the data from columns to get transposed into rows. But I do not want to convert rows…
kpanse
  • 31
  • 1
  • 4
1 2 3
…
90 91