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

sql update based on column names

I have a problem. I have T1, T2, T_Join tables. T_Join: first column: ID (unique) e.g.: 10,11,12,13. Second column: CODE, it contains attributes which are equals to the column names of T2. E.g.: type, source, section, importance. These are…
A117
  • 319
  • 1
  • 3
  • 8
7
votes
1 answer

Build SQL query with dynamic columns

My tables go as follows: Patients table PatientId Name 1 James ... Visits table Date PatientID_FK Weight 1/1 1 220 2/1 1 210 ... How can I build a query that returns PatientId Name …
Blaise
  • 21,314
  • 28
  • 108
  • 169
7
votes
3 answers

Is Unpivot (Not Pivot) functionality available in Linq to SQL? How?

I have seen posts which would fetch you pivot results but not unpivot, Need to know if there is any clean way to achieve ? If not the any workaround would do as well ? Execute this to see unpivot results in Management Studio CREATE TABLE…
Pravin Pawar
  • 2,559
  • 3
  • 34
  • 40
6
votes
2 answers

Is there a melt command in Snowflake?

Is there a Snowflake command that will transform a table like this: a,b,c 1,10,0.1 2,11,0.12 3,12,0.13 to a table like this: key,value a,1 a,2 a,3 b,10 b,11 b,13 c,0.1 c,0.12 c,0.13 ? This operation is often called melt in other tabular systems,…
Haterind
  • 1,095
  • 1
  • 8
  • 16
6
votes
3 answers

Pandas 'partial melt' or 'group melt'

I have a DataFrame like this >>> df = pd.DataFrame([[1,1,2,3,4,5,6],[2,7,8,9,10,11,12]], columns=['id', 'ax','ay','az','bx','by','bz']) >>> df id ax ay az bx by bz 0 1 1 2 3 4 5 6 1 2 7 8 9 10 …
Kirk Broadhurst
  • 27,836
  • 16
  • 104
  • 169
6
votes
3 answers

Oracle show columns as row

How can i convert multiple column to row accoding by followed sample? For solution, i inspected many sample for example pivot, decode etc. but i did not reach absolute solution. +---------+---------+---------+---------+ | COL_1 | COL_2 | COL_3 …
user3595082
6
votes
1 answer

TSQL Multiple column unpivot with named rows possible?

I know there are several unpivot / cross apply discussions here but I was not able to find any discussion that covers my problem. What I've got so far is the following: SELECT Perc, Salary FROM ( SELECT jobid, Salary_10 AS Perc10, Salary_25 AS…
ksauter
  • 112
  • 1
  • 7
6
votes
2 answers

Change the name of columns after they became rows values in unpivot query SQLSERVER2008

I have table with the following information: CREATE TABLE [dbo].[ApprovedPalmsInFarm]( [id] [int] IDENTITY(1,1) NOT NULL, [FarmID] [nvarchar](10) NOT NULL, [kalas] [int] NULL, [khnizi] [int] NULL The default result of select query…
amal50
  • 981
  • 2
  • 21
  • 35
6
votes
1 answer

SQL Pivot with dynamic generated columns, aggregate function and columns without aggregation

I have got the following query: WITH preEKBE AS( SELECT EKPO . MANDT, EKPO . EBELN, EKPO . EBELP, DD07T.DDTEXT AS c_Meaning, EKBE . VGABE, EKBE . DMBTR, EKBE . MENGE, COUNT(VGABE)…
JollyPopper
  • 85
  • 1
  • 8
6
votes
3 answers

Dynamic fieldnames in subquery?

I have a table similar to the following: CREATE TABLE stats ( name character varying(15), q001001 numeric(9,0), q001002 numeric(9,0), q001003 numeric(9,0), q001004 numeric(9,0), q001005 numeric(9,0) ) I need to query this table for the…
user3648665
  • 109
  • 4
6
votes
2 answers

TSQL - Help with UNPIVOT

I am transforming data from this legacy table: Phones(ID int, PhoneNumber, IsCell bit, IsDeskPhone bit, IsPager bit, IsFax bit) These bit fields are not nullables and, potentially, all four bit fields can be 1. How can I unpivot this thing so that…
Gus Cavalcanti
  • 10,527
  • 23
  • 71
  • 104
6
votes
2 answers

How can I get around differences in column types when using unpivot?

I am having problems using unpivot on columns, that are not the exact same datatype, and I can't figure out how to convert the columns on the fly, because the syntax for UNPIVOT does not seem to support it. Consider this example: DECLARE @People…
Kjensen
  • 12,447
  • 36
  • 109
  • 171
6
votes
2 answers

Display two column values as two row in SQL

I am using SQL Server 2012 for my database. Now, I have one table with following details. ID COLUMN1 COLUMN2 1 A B Now i want result like this. ID Values 1 …
Brijesh Patel
  • 2,901
  • 15
  • 50
  • 73
6
votes
4 answers

SQL Server convert columns to rows

I have a sql table with current value and previous value. Id Value1 PValue1 Value2 PValue2 1 A A V V1 2 B B1 W W1 3 C C1 X X I want to compare them and display in a the following table if…
developer
  • 1,401
  • 4
  • 28
  • 73
6
votes
5 answers

How to convert columns to rows in sql server

Please consider this table: ID Page Line C01 C02 C03 --------------------------------------------------------------------- 1 122 11 1 0 1 1 …
Arian
  • 12,793
  • 66
  • 176
  • 300
1 2
3
…
90 91