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

SQL Server : Columns to Rows

Looking for elegant (or any) solution to convert columns to rows. Here is an example: I have a table with the following schema: [ID] [EntityID] [Indicator1] [Indicator2] [Indicator3] ... [Indicator150] Here is what I want to get as the result: [ID]…
Sergei
  • 1,745
  • 2
  • 11
  • 6
148
votes
3 answers

Unpivot with column name

I have a table StudentMarks with columns Name, Maths, Science, English. Data is like Name, Maths, Science, English Tilak, 90, 40, 60 Raj, 30, 20, 10 I want to get it arranged like the following: Name, Subject, …
Tilak
  • 30,108
  • 19
  • 83
  • 131
55
votes
10 answers

Equivalent to unpivot() in PostgreSQL

Is there a unpivot equivalent function in PostgreSQL?
Tony Searle
39
votes
5 answers

Convert matrix to 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize')

I need to convert the Excel matrix FIRST in the table LATER: FIRST: P1 P2 P3 P4 F1 X F2 X X F3 X X F4 X X LATER: F P VALUE F1 P1 X F1 P2 F1 P3 F1 P4 F2 P1 X F2 P2 …
user3095042
  • 401
  • 1
  • 5
  • 4
38
votes
10 answers

How do you create a "reverse pivot" in Google Sheets?

I am trying to produce a "reverse pivot" function. I have searched long and hard for such a function, but cannot find one that is already out there. I have a summary table with anywhere up to 20 columns and hundreds of rows, however I would like to…
Marc Henson
  • 407
  • 1
  • 5
  • 7
35
votes
9 answers

SQL Server - Include NULL using UNPIVOT

UNPIVOT will not return NULLs, but I need them in a comparison query. I am trying to avoid using ISNULL the following example (Because in the real sql there are over 100 fields): Select ID, theValue, column_name From (select ID, …
JeffO
  • 7,957
  • 3
  • 44
  • 53
30
votes
2 answers

Unpivot in Spark SQL / PySpark

I have a problem statement at hand wherein I want to unpivot table in Spark SQL / PySpark. I have gone through the documentation and I could see there is support only for pivot, but no support for un-pivot so far. Is there a way I can achieve…
Manish Mehra
  • 1,381
  • 1
  • 16
  • 24
30
votes
3 answers

Mysql Convert Column to row (Pivot table )

I have a table like this id month col1 col2 col3 col4 101 Jan A B NULL B 102 feb C A G E And then I want to create report like this desc jan feb col1 A C col2 B A col3 0 G Col4 B E Can anyone help with this?
user1914516
  • 339
  • 1
  • 3
  • 3
28
votes
3 answers

Is it possible to have multiple pivots using the same pivot column using SQL Server

I am facing the following challenge. I need to rotate table data twice over the same column. Here's a screenshot of the data. I want to have one row for each Item ID containing both the purchasing value and the selling value for each year. I tried…
Rob Vermeulen
  • 1,910
  • 1
  • 15
  • 22
27
votes
1 answer

SQL transpose full table

I need to do the following transpose in MS SQL from: Day A B --------- Mon 1 2 Tue 3 4 Wed 5 6 Thu 7 8 Fri 9 0 To the following: Value Mon Tue Wed Thu Fri -------------------------- A 1 3 5 7 9 B 2 4 6 8 0 I…
Selrac
  • 2,203
  • 9
  • 41
  • 84
25
votes
3 answers

SQL Server Pivot Table with multiple column aggregates

I've got a table: create table mytransactions(country varchar(30), totalcount int, numericmonth int, chardate char(20), totalamount money) The table has these records: insert into mytransactions(country, totalcount, numericmonth, chardate,…
codingguy3000
  • 2,695
  • 15
  • 46
  • 74
24
votes
3 answers

MySQL - How to unpivot columns to rows?

ID | a | b | c 1 | a1 | b1 | c1 2 | a2 | b2 | c2 How do I reorganize the rows as ID, columntitle, value? 1 | a1 | a 1 | b1 | b 1 | c1 | c 2 | a2 | a 2 | b2 | b 2 | c2 | c
user2128539
  • 241
  • 1
  • 2
  • 5
23
votes
3 answers

SQL Unpivot multiple columns Data

I am using SQL server 2008 and I am trying to unpivot the data. Here is the SQL code that I am using, CREATE TABLE #pvt1 (VendorID int, Sa int, Emp1 int,Sa1 int,Emp2 int) GO INSERT INTO #pvt1 VALUES (1,2,4,3,9); GO --Unpivot the table. SELECT…
user1005310
  • 737
  • 2
  • 12
  • 40
21
votes
4 answers

SQL Server unpivot multiple columns

I'm trying to pivot a table around it's many columns to get to 3 columns (pivot, column name, value) so for example: name | age | gender ------+-------+--------- John | 20 | M Jill | 21 | F would become: name | column |…
mathematician
  • 1,942
  • 5
  • 19
  • 22
21
votes
2 answers

Transposing Dynamic Columns to Rows

I'd like to know how to unpivot Table_1 into Expected_Result_Table: Table1 ----------------------------------------- Id abc brt ccc ddq eee fff gga hxx ----------------------------------------- 12345 0 1 0 5 0 2 0 0 21321…
Romualdo Alves
  • 271
  • 1
  • 2
  • 8
1
2 3
…
90 91