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

UNPIVOT and JOIN using col and row numbers

I have a data of order per costumer and per date that I achieved to present in the following way: ID Date Record ColNumber RowNumber ---------------------------------------------------------------- ColNumber / RowNumber being the…
endlessend2525
  • 11
  • 1
  • 2
  • 7
-2
votes
1 answer

sql server pivot using date to order data in columns

My tables combined look something like: Seller_ID |From |To |Depart_Date |Arrival_Date ------------------------------------------------------------------------------------- Paul |office |client_23 …
ruisadias
  • 17
  • 3
-2
votes
2 answers

Pivot/Unpivot multiple columns

How do I get the data: OrderID | Ver | Res | OI | Ret | Corp ----------------------------------------------- 1 | 'BUD' | 57823 | 11111 | 34304 | 12344 2 | 'ACT' | 12345 | 23456 | 45678 | NULL To Ver1 | Res1 | OI1 | Ret1…
Glen
  • 802
  • 1
  • 11
  • 27
-2
votes
1 answer

IMDb Ratings: Pivot/Unpivot

I'm working on a personal project where I'm looking at IMDb movie ratings across a group of friends. I am trying to create a fact table that has each movie and the ratings for each rater for each movie (NULL if no rating). Here is what I'm currently…
Walker
  • 153
  • 2
  • 9
-2
votes
1 answer

Include column names in the results of an unpivot query

We have this UNPIVOT query. SELECT Value FROM (Your select statement) as x UNPIVOT (Value FOR val IN (a, b, c, d)) as p That produces results like this: Value value1 value2 value3 value4 How can we elaborate to include the column names? Value …
Shaun Luttin
  • 133,272
  • 81
  • 405
  • 467
-2
votes
1 answer

How do i can convert Column Values Into Row?

Here I'm having table 'TABLE_1' as |ID | Name | ACID1 | ACVALUE1 | ACID2 | ACVALUE2 | ACID3 | ACVALUE3 | |---------------------------------------------------------------------- | 1 |ABC | 10 | 82.50 | 20 | 175.95 | 40 | 125.75 …
Rupesh S
  • 125
  • 1
  • 14
-2
votes
3 answers

SQL Query to Transpose Column Counts to Row Counts

I have a table that looks like the following which shows the count of types. I need to and have been trying to display data as 1 column and 7 rows instead though... without…
BooneStars
  • 3
  • 5
-2
votes
2 answers

SQL server using unpivot function

How would you obtain the following output using SQL? Type: Value Type2 Value2 p_01 1 ofpk_01 1 p_02 2 ofpk_02 2 p_03 3 ofpk_03 3 Table structure create table test(p_01 int, …
Raju Kumar
  • 1,255
  • 3
  • 21
  • 39
-2
votes
1 answer

Simple SQL Query -

I have data in a SQLServer table like this: ID Name Year Value -- ---- ---- ----- 2 Ted 2013 2000 2 Ted 2012 1000 I need the view syntax to output this: ID Name Yr1 Value1 Yr2 Value2 -- ---- --- …
kanderson
  • 5
  • 5
-2
votes
1 answer

T-SQL Pivot/Unpivot(Transpose) Column Headers Needed as Data Rows

I'm working on a T-SQL issue where I needed to Transponse Rows into Columns and using UNPIVOT and PIVOT together per a post at Simple way to transpose columns and rows in Sql? No problem. It does Exactly what I want with the row/column…
JTSOne
  • 169
  • 5
  • 19
-2
votes
1 answer

Switching rows for columns and columns for rows

I have query like this: SELECT Column, SUM(Row1) AS Row1, SUM(Row2) AS Row2, SUM(Row3) AS Row3, SUM(Row4) AS Row4, SUM(Row5) AS Row5, SUM(Row6) AS Row6, SUM(Row7) AS Row7, …
formatc
  • 4,261
  • 7
  • 43
  • 81
-2
votes
1 answer

Convert sqlserver data to Pivot table

ID ---- ACCOUNT ---- SECTOR ---- AMOUNT_CURRENCY1 ------ AMOUNT_CURRENCY2 1 --- account1 ---- sector1 --- 100 ------ 200 2 --- account1 ---- sector2 --- 150 ------ 250 3 --- account2 ---- sector1 --- 250 …
Abady
  • 111
  • 1
  • 2
  • 10
-2
votes
2 answers

Rows into Columns by using pivot

Can you help me to solve this error The type of column "nvrchildgender" conflicts with the type of other columns specified in the UNPIVOT list. Table structure: CREATE TABLE [dbo].[tblHRIS_ChildDetails]( [intCHID] [int], [intSID] [int] NOT…
Kajah User
  • 593
  • 5
  • 17
  • 56
-3
votes
2 answers

Transpose columns into row in MS SQL Server - dynamic UNPIVOT

I have an MS SQL Server table that looks like this: Example SQL table structure I have employees in each row, some columns that describe them and then columns where I have Quota and Achievement for fiscal quarters. As you can guess, new fiscal…
ghgma6
  • 1
  • 1
-3
votes
1 answer

SQL SERVER Upivot

I'm trying to unpivot this table but I always get an error. Does somebody a solution for this ? The result has to be like this, 3 columns ID / question (q) / total number of these row enter image description here
Anthony de Becker
  • 11
  • 4
1 2 3
…
90
91