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
17
votes
3 answers

Select values from multiple columns into single column

I have a table in a database that has 9 columns containing the same sort of data, these values are allowed to be null. I need to select each of the non-null values into a single column of values that don't care about the identity of the row from…
Jason
  • 3,599
  • 10
  • 37
  • 52
16
votes
3 answers

Split Multiple Columns into Multiple Rows

I have a table with this structure. UserID | UserName | AnswerToQuestion1 | AnswerToQuestion2 | AnswerToQuestion3 1 | John | 1 | 0 | 1 2 | Mary | 1 | 1 | 0 I…
Sandro
  • 4,761
  • 1
  • 34
  • 41
14
votes
4 answers

Oracle 11g: Unpivot multiple columns and include column name

I'm triyng to unpivot multiple columns in my dataset. Here's what my data look like. CREATE TABLE T5 (idnum NUMBER,f1 NUMBER(10,5),f2 NUMBER(10,5),f3 NUMBER(10,5) ,e1 NUMBER(10,5),e2 NUMBER(10,5) ,h1…
Tommy O'Dell
  • 7,019
  • 13
  • 56
  • 69
12
votes
4 answers

How to unpivot in BigQuery?

Not sure what functions to call, but transpose is the closest thing I can think of. I have a table in BigQuery that is configured like this: but I want to query a table that is configured like this: What does the SQL code look like for creating…
Ben Leathers
  • 184
  • 1
  • 2
  • 9
11
votes
7 answers

Pandas: How can I move certain columns into rows?

Suppose I have the df below. I would like to combine the price columns and value columns so that all prices are in one column and all volumes are in another column. I would also like a third column that identified the price level. For example,…
IamWarmduscher
  • 875
  • 2
  • 10
  • 27
11
votes
3 answers

How to simulate UNPIVOT in Access?

UNPIVOT is available in MS SQL-Server 2005, but AFAIK not in MS Access 2010. How can it be implemented with on-board means? For example, I have a table ID | A | B | C | Key 1 | Key 2 | Key 3 --------------------------------------- 1 | x | y | z | …
Andreas Spindler
  • 7,568
  • 4
  • 43
  • 34
10
votes
2 answers

Redshift - Many Columns to Rows (Unpivot)

In Redshift : I've a table with 30 dimension fields and more than 150 measure fields. To make good use of these data in a visualization tool (Tableau), I need to Unpivot the measure columns into only one measure and one dimension to categorize…
Thomas Olivier
  • 101
  • 1
  • 1
  • 4
10
votes
1 answer

SQL Server Pivot Table with Counts and Sums

I am trying to get an SQL Server Pivot table to work that allows me to count and then sum a number of columns (6 in total). The purpose of the pivot table is to aggregate online questionnaire results for any number of production sites. There are 6…
Andy5
  • 2,319
  • 11
  • 45
  • 91
9
votes
2 answers

SQL Select to return a static list of values?

Is there a way to perform a select and return a static list? Something like the following: select * from ('CA', 'FB', 'FC') It should return CA FB FC
Bill
  • 915
  • 2
  • 13
  • 23
8
votes
2 answers

Postgres: convert single row to multiple rows (unpivot)

I have a table: Table_Name: price_list --------------------------------------------------- | id | price_type_a | price_type_b | price_type_c | --------------------------------------------------- | 1 | 1234 | 5678 | 9012 | |…
skybunk
  • 833
  • 2
  • 12
  • 17
8
votes
3 answers

Handle NULL value in UNPIVOT

I'm able to unpivot a table but null values are not included in the result. create table pivot_task ( age int null, [a] numeric(8,2), [b] numeric(8,2), [c] numeric(8,2), [d] numeric(8,2), [e] numeric(8,2) ); select * from pivot_task; insert into…
Vishwanath Dalvi
  • 35,388
  • 41
  • 123
  • 155
8
votes
1 answer

How to join row values to column names in a dynamic query

I am developing an application that allows configurable questions and answers. Currently there can be up to 20 answers, but possibly less. The structure I have is as follows: Questions +----+--------+--------------+-------------+ | ID | FormId |…
Yetiish
  • 703
  • 1
  • 8
  • 19
8
votes
1 answer

Using PIVOT to Flip Data from Wide to Tall

I have a table that is rather wide that I would like to convert to tall. The data currently resides like this: VEND YEAR I1_DOLS I1_QTY I2_DOLS I2_QTY I3_DOLS I3_QTY ... 1234 2011 101587 508 203345 334 105938 …
Russell Byrne
  • 93
  • 1
  • 1
  • 3
8
votes
1 answer

SQL query ; horizontal to vertical

I'm stuck with a SQL query (SQL Server) that involves converting horizontal rows to vertical rows Below is my data No Flag_1 Flag_2 Flag_3 --- ---- ----- ----- A 1 2 3 B 4 1 …
user1141584
  • 619
  • 5
  • 16
  • 29
7
votes
2 answers

SQL Server unpivot two columns

I'm trying to pivot a table to get 3 columns my example table is like : CREATE TABLE tbl1 (A1 int, cA1 int,A2 int, cA2 int,A3 int, cA3 int) GO INSERT INTO tbl1 VALUES (60,2,30,3,10,5); GO I am using the query below to get tthe results from two…
Andy L.
  • 237
  • 1
  • 3
  • 12
1
2
3
…
90 91