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
1 answer

How to use unpivot for multiple columns with constants/missing column?

I'm trying to use the UNPIVOT in order to avoid the old-fashioned way of multiple unions The rationale for this is I'm collecting some data (lab test, result, unit, ...) I'd like to display in a appropriate way for reviewing them Let's say, for…
Julien
  • 77
  • 4
2
votes
1 answer

How to transpose and split in Google Apps Script?

I am using 2 sheets, rawData and processedData. rawData looks like this: Title Options Title1 Option1, Option2, Option3, Option4 Title2 Option1, Option2, Option3, Option4, Option5 Title3 Option1, Option2, Option3 processedData should look like…
kamen1111
  • 175
  • 10
2
votes
2 answers

Is it possible to retrieve the data from table in transpose format

I have a table which has data in the below format. id | col1 | col2 | col3 1 | d11 | d21 | d31 2 | d12 | d22 | d32 3 | d13 | d23 | d33 4 | d14 | d24 | d34 5 | d15 | d25 | d35 6 | d16 | d26 | d36 Is it possible to get the data in…
Eeshwar Ankathi
  • 260
  • 3
  • 11
2
votes
1 answer

Reducing number of columns by transposing

I have a table which has over 60 columns. I want to reduce the number of columns and already have an idea. So the columns look like this: A_yes A_no B_yes B_no C_yes C_no 1 4 3 5 9 2 What I wish to get is this Category yes …
Johnny Banana
  • 123
  • 1
  • 9
2
votes
2 answers

Python unpivot dataframe with duplicate column names

I have a huge excel file with data like this: DATE OIL GAS WATER OIL GAS WATER 01-01-2018 100 200 300 10 20 30 01-02-2018 200 500 300 30 40 10 01-03-2018 10 50 30 20 40 …
naren b
  • 21
  • 2
2
votes
2 answers

when more than one condition satisfies, how to group them to calculate average

I'm using Oracle database and want to calculate the mean product price for different regions. Example: prod,amt,price X,100,1 X,180,2 X,250,3 Y,90,2 Y,170,3 Y,280,3 Here product X is sold for 100 in one region and 180 in another region..etc. Now,…
stack0114106
  • 8,534
  • 3
  • 13
  • 38
2
votes
2 answers

Cross apply on columns on SQL server. Syntax error near )

I am trying to unpivot several columns, but I can't find the way of solving the syntax error. It says incorrect syntax near ')'. Here is the code: SELECT dates, times, locations, events FROM mytable CROSS APPLY (VALUES ('instance1',…
zhivab
  • 99
  • 11
2
votes
2 answers

Using dynamic unpivot with columns with different types

i have a table with around 100 columns named F1, F2, ... F100. I want to query the data row-wise, like this: F1: someVal1 F2: someVal2 ... I am doing all this inside a SP, therefore, I am generating the sql dynamically. I have successfully…
ravi kumar
  • 1,548
  • 1
  • 13
  • 47
2
votes
2 answers

Using UNPIVOT in constructed String Query Dynamic

I am building a query where I will need a UNPIVOT on dynamic columns. (abcd are example string name) data1 data2 com fr random 1 2 a d sq 3 4 b a fd UNPIVOT like so : data1 data2 Name Website random 1 2 a …
Benoît
  • 143
  • 1
  • 2
  • 15
2
votes
3 answers

row to column data

I have table named AA. I have 3 column in it namely x,y,z . it has exactly one rows. select * from aa; x y z 10 20 30 I want output like 10 20 30 I have used below query select x from AA union all select y from AA union all select z from AA…
user9892866
2
votes
2 answers

PIVOT or UNPIVOT: Will either work?

I have looked around for examples that are specifically like what I have but I just can't find anything. From what I've seen I'm not sure it's possible but I figured I'd ask here first. 1. Original Table I have a table that looks like this:…
B.Scar
  • 47
  • 5
2
votes
2 answers

Merge multiple columns values in one column in one row Oracle SQL

I have got multiple columns that needs to be aggregated into column in a single row. Before Table name: columnMerger colNum col1 col2 col3 1 a b c After colNum col1234 1 a, b, c Step 1, I used unpivot to bring all in…
JustLift
  • 153
  • 1
  • 7
  • 17
2
votes
1 answer

How to use dynamic SQL to add value of 2 columns

I have small table which contains students marks. Table data is shown in below image. It is look like below in excel I want to calculate the total using dynamic SQL. I don't want to update it. However, I just want to select all the data with…
Jay Desai
  • 821
  • 3
  • 15
  • 42
2
votes
3 answers

How to Pivot a Table?

My table has this structure subcode date rol1 rol2 rol3 rol4 rol5 rol6 upto rol60 -------------------------------------------------------------- mc1603 12/03/2011 p p a p p p a mc1604 12/03/2011 p p a …
arp
  • 21
  • 1
2
votes
1 answer

SQL Server group by ID & dynamic Pivot/Transpose attributes

I have seen a Dynamic SQL answer that was VERY similar to my problem here, but I couldn't wrap my head around the small change that would get me to the finish line. I am trying to illustrate all of the stores you will need to travel to in order to…
iLLy_Mays
  • 23
  • 2
1 2 3
…
90 91