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

Unpivot tables script error

I have a table called rp_resolution_master. CREATE TABLE "REQUEST_PORTAL"."RP_RESOLUTION_MASTER" ( "RM_ID" NUMBER, "SR_ID" NUMBER, "REQUEST_STATUS" VARCHAR2(200 BYTE), "COMMENTS" VARCHAR2(4000 BYTE), "UPDATED_ON" DATE, …
Gautam Anand
  • 51
  • 1
  • 3
3
votes
2 answers

T-sql pivot function

I need to convert the following table quarter cal_year blue green yellow red DEC 2011 +31% 25-30% 22-24% -21% MAR 2012 +61% 50-60% 43-49% -42% into this. Is there a simple way to achieve it? Color DEC MAR …
user1672932
  • 129
  • 1
  • 2
  • 9
3
votes
3 answers

Unpivot table with multiple columns and dynamic column names

I am trying to unpivot a table with multiple rows and columns. Each row needs to be extratced to 2 rows with specific columns and the column names need to be renamed and a new column needs to added based on the columns selected! I am including…
stevenmahony
  • 89
  • 1
  • 1
  • 9
3
votes
1 answer

T-SQL VIEW - CTE + UNPIVOT versus UNIONs versus other tehnique

I wonder which solution is better. I have to declare some variables in view, which are calculated using T-SQL date functions (DATEADD,DATEPART,GETDATE(),etc). After some research I've write this: WITH…
gotqn
  • 42,737
  • 46
  • 157
  • 243
3
votes
3 answers

Unpivot dataset using C#

How to convert dataset columns into rows using C# as unpivot in SQL. I found a method to convert rows to columns . C# Rows to Columns . Any ideas to convert DataTable columns to rows ?? Thanks .. My code as Ivan mentioned below OracleDatabase…
kk1076
  • 1,740
  • 13
  • 45
  • 76
2
votes
2 answers

Oracle UNPIVOT and SYSDATE giving weird results

I am trying to transpose columns to rows using query similar to the following... WITH query AS ( SELECT SYSDATE AS SomeDate, 'One' AS One, 'Two' AS Two, 'Three' AS Three, 'Four' AS…
Chandu
  • 81,493
  • 19
  • 133
  • 134
2
votes
4 answers

GROUP BY or COUNT Like Field Values - UNPIVOT?

I have a table with test fields, Example id | test1 | test2 | test3 | test4 | test5 +----------+----------+----------+----------+----------+----------+ 12345 | P | P | F | I | P So for each…
Phill Pafford
  • 83,471
  • 91
  • 263
  • 383
2
votes
1 answer

using a sub-query to populate unpivot columns list

I currently do not have access to a server to test this out on, but how would I go about doing something like the following UNPIVOT (X for XY IN ( SELECT column_name AS [XY] FROM Information_schema.Columns WHERE Table_name = XYZ AND…
user692898
  • 109
  • 2
  • 8
2
votes
4 answers

Postgres columns to rows

I have a PostgreSQL table which gives me the following output: country metric_code male female pensioners teenagers us metric_1 10 14 22 30 us metric_2 105 142 222 309 uk metric_1 15 13 9 21 uk metric_2 212 264 440 165 create table…
Maximusrs
  • 41
  • 3
2
votes
3 answers

Cross tab a Microsoft SQL Server table that has multiple IDs - a 2x2 grid of each variable ID

I have a table in Microsoft SQL Server like the…
Shawn
  • 21
  • 2
2
votes
5 answers

How to search in columns in SQL

I have two table,the names table and the earnings table. Name_ID Name_desc 1 mark 2 smith 3 becky 4 jimmy and the earnings…
Petersselorse
  • 53
  • 6
2
votes
0 answers

Oracle SQL - Count records identical on a subset of columns and display values of other columns for which they differ

I am trying to write a query that given a table like the following…
lucia de finetti
  • 219
  • 2
  • 8
2
votes
1 answer

How to Unpivot a Pandas PivotTable?

I have the following pandas pivot table, and would like to shift only the 'Date' Column Header to a row. Date JAN2022 FEB2022 ID Income Expenses Income Expenses 0 A 100 -23 10 -53 1 B …
Denzyl
  • 166
  • 1
  • 11
2
votes
2 answers

Unpivot multiple variables in a python pandas dataframe

I've got a big dataframe which shows the amount of each product and their costs for different products. However, I want to transform (unpivot) the dataframe into a long dataframe with each product name as an ID and their amounts and costs in two…
salijo
  • 23
  • 2
2
votes
1 answer

How to unpivot multiple columns in PySpark?

The columns CGL, CPL, EO should become Coverage Type, the values for CGL, CPL, EO should go in column Premium, and values for CGLTria,CPLTria,EOTria should go in column Tria Premium. declare @TestDate table ( QuoteGUID…
Gaurav Kumar
  • 21
  • 1
  • 3
1 2 3
…
90 91