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

Return Columns as Rows for Single Record

I've read many posts on this site as well as others regarding using PIVOT, UNPIVOT, UNION and CROSS JOIN but I can't quite seem to get the results I'm looking for. My table looks like the following: EmployeeName Salary Address City …
aantiix
  • 516
  • 2
  • 9
  • 24
-1
votes
1 answer

Unpivot in view causes error with conversion into date when date used as criteria

I have a table that has one row per month and amounts are stored in different columns (DAY1, DAY2... DAY31). I have created a view that uses unpivot to split this into one row per day, so that I can do calculations for given date range. When I try…
James Z
  • 12,209
  • 10
  • 24
  • 44
-1
votes
1 answer

Efficiently reformat data layout

I have several Excel spreadsheets with data layout like this raw data: company company1 company2 company3 currency $ Y E 1/1/2013 32.68 12 3 1/2/2013 12.5 13 4 1/3/2013 45 45 …
Xinxin Li
  • 51
  • 1
  • 1
  • 9
-1
votes
1 answer

Postgres pivot columns to rows

How can I convert table of the following 5 columns structure: Id, name, col1, col2, col2 1 aaa 10 20 30 2 bbb 100 200 300 to the following structure where Col1, Col2 and Col3 columns are now shown as strings in new columns…
user1409708
  • 953
  • 2
  • 11
  • 20
-1
votes
1 answer

SQL Pivot table columns to rows

Im trying to convert this table: Date |Col1 |Col2 |Col3|Col4|Col5|Col6|Col7|Col8 2/15/2015|Product1|MTD |1 |2 |3 |4 |5 |6 2/15/2015|Product1|QTD |11 |22 |33 |44 |55 |66 2/15/2015|Product1|YTD |111 |222 |333 |444 |555 |666…
Carter
  • 3
  • 2
-1
votes
1 answer

Unpivot the data based on certain conditions in SQL

This is my table looks like country ID weekyear W1 W2 W3 ------------------------------------------------------------ aa 1000 322015 0.00 4.50 0.00 bb 2000 262015 26.85 5.78 1.23 cc …
Shyam Pandian
  • 35
  • 12
-1
votes
2 answers

Unpivoting movie genres

I'm looking at some movie data from IMDb and I have found a limitation in my knowledge. Here is the data I'm looking at: I think I will run into problems with this current table. How can I get it so that there are two columns, MovieID and Genre? It…
Walker
  • 153
  • 2
  • 9
-1
votes
1 answer

Transform row to column in sql

I have table like this: PC(code, model, speed, ram, hd, cd, price) when i run the query : select model,speed,ram,hd,cd,price from PC where code=(select max(code) from PC) i get the result as below: model speed ram hd cd …
sajju217
  • 447
  • 3
  • 7
  • 19
-1
votes
1 answer

Transposing columns in R programming

In my case the given input is like this Country 1990 1991 1992 Bolivia 5 6 7 Cambodia 8 9 10 Russia 11 12 13 They want me to unpivot like this: Country Variable of Interest Year Bolivia 5 …
Gowtham SB
  • 332
  • 1
  • 3
  • 16
-1
votes
2 answers

PivotTable for questionnaire analysis

I have some data from a reply form that I need to compile. I have some questions that have values from 1-5 or No-Yes. Example data: Date Question1 Question2 Question3 2010-12-01 4 3 No 2010-12-01 5 5 …
user1564762
  • 745
  • 2
  • 11
  • 18
-1
votes
1 answer

Horizontal Count SQL

I apologize if this is a duplicate question but I could not find my answer. I am trying to take data that is horizontal, and get a count of how many times a specific number appears. Example table +-------+-------+-------+-------+ | Empid | KPI_A |…
Woodrow Bartlett
  • 1
  • 1
-1
votes
1 answer

How to transpose/pivot SQL table

I have a SQL table like below: ID StepID Rating Comments StaffID Date ------------------------------------------------------------- 1 1 6 blah blah 1025 2014-03-20 1 2 6 blah blah …
viv_acious
  • 2,429
  • 9
  • 34
  • 55
-1
votes
2 answers

how to insert few columns data into a single row of other table in sql server

I want few columns of a table data into a single column of other table with out using union all. For example, if the original table is: acct col1 col2 col3 1 x y z 2 a b c I want a new table (with…
user3319177
  • 1
  • 2
-1
votes
1 answer

How to convert Column header to Row for loannumber

I am stuck in unpivoting. I have a table like #temp below. Using sql server 2008 r2 Select LoanNumber = 2000424385 ,[AmntType1] = 120.32 ,[AmntType2] = 131.52 ,[AmntType3] = 142.36 into #temp select * from #temp Above table has…
Gufran Khan
  • 63
  • 1
  • 7
-1
votes
1 answer

Split comma-separated entries to new rows (No VBA)

I currently have this data in a sheet: Col A Col B 105 399, 400 207 405,406,407 and want to split the comma-separated entries in the second column and insert them in new rows as below: Col A Col B 105 399 400 207 405 …
Dan
  • 9
  • 4
1 2 3
…
90 91