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
4
votes
7 answers

Oracle 11 SQL : Split 1 row into x rows and insert a new column

I asked Oracle 11 SQL : Is there a way to split 1 row into x rows -- this question is very close to that but has a small twist ... Customer asked to Split 1 row from the Oracle DB SQL into 6 rows. Let's say, originally the SQL (complex sql with…
anjanb
  • 12,999
  • 18
  • 77
  • 106
4
votes
3 answers

SQL - Union All Users in One Table

Table : Popular UserName FriendName -------- ---------- John Sarah Philip Ursula John Marry John Jeremy Philip Brock Khan Lemy And I want list with query; John Philip Khan -------- ---------- …
Hasan Kaan TURAN
  • 391
  • 2
  • 13
4
votes
6 answers

How to unpivot columns and GROUP BY based on values in SQL Server?

I want to calculate distinct PID and VID counts per year. Condition: 1. Separate count for column A or B or C or D having value 1 ( A_to_D) 2. Separate count for column E having value 1 (E) 3. Separate count for column F having value 1 …
rohit patil
  • 159
  • 2
  • 9
4
votes
2 answers

Unstack dataframe and keep columns

I have a DataFrame that is in a too much "compact" form. The DataFrame is currently like this : > import numpy as np > import pandas as pd > df = pd.DataFrame({'foo': ['A','B'], 'bar': ['1', '2'], 'baz': [np.nan,…
Rémi
  • 43
  • 5
4
votes
1 answer

Converting columns to rows (UNPIVOT) in hiveql

I have a table with a structure like this: column1, column2, column3, X1, X2, X3, X4 A1, A2, A3, 5, 6, 1, 4 I would like to convert this into column1, column2, column3, Key, Value A1, A2, A3, X1, 5 A1, A2,…
NG Algo
  • 3,570
  • 2
  • 18
  • 27
4
votes
3 answers

SQL convert from 3 to 2 columns

I am trying to convert 3 columns into 2. Is there a way I can do this with the example below or a different way? For example. Year Temp Temp1 2015 5 6 Into: Year Value Base 5 2015 6
TheProgrammer
  • 1,314
  • 5
  • 22
  • 44
4
votes
2 answers

Pivoting row/column using T-SQL

How would be the T-SQL query for below scenario: Select * from Table1 col1|col2|col3 -------------- xxxx|1111|2222 yyyy|3333|4444 to col1|col2 --------- xxxx|yyyy 1111|3333 2222|4444
p2k
  • 2,126
  • 4
  • 23
  • 39
4
votes
3 answers

Single row to multiple columns and rows

I have a SQL Server table called Test with this sample data: LineNo BaseJanuary BaseFebruary BudgetJanuary BudgetFebruary 1 10000 20000 30000 40000 2 70000 80000 90000 100000 I…
Chilli
  • 53
  • 3
4
votes
2 answers

TSQL - Unpivot multiple columns

How can I unpivot multiple columns in "one"? Right now I have an unpivot for each column but this creates a lot of empty rows. See the screenshot please. At the top you see the input data. At the moment I'm at the table in the middle with this…
SvenB
  • 139
  • 3
  • 10
4
votes
3 answers

Sql Server: How do I unpivot with an alias?

I know that you can use an alias on a column with pivot, but I'd like to use an alias with unpivot as well. select UserId , ContactMethod , ContactMethodValue from Users unpivot ( ContactMethodValue for ContactMethod in ( …
Master Morality
  • 5,837
  • 6
  • 31
  • 43
4
votes
1 answer

Row into column SQL Server 2005/8

I've just started to get into SQL Server deeper and I have a problem. I have to transform a row into a column, but I can't figure it out. The row looks like this: Columns: T1 T2 T3 .........T20 Values: 1 0 9 ......... 15 I want…
darkdante
  • 707
  • 1
  • 17
  • 36
4
votes
2 answers

SQL combine column names and data types with column values

I am using Sql Server 2012 for my DBMS. In my database I have a Product table that is related to a series of catalog tables. These catalog tables represent the various Product Categories. The idea being that while all products have certain…
trenthaynes
  • 1,668
  • 2
  • 16
  • 28
4
votes
2 answers

Taking the "transpose" of a table using SQL

I don't know if there is a name for this operation but it's similar to the transpose in linear algebra. Is there a way to turn an 1 by n table T1 such as c_1|c_2|c_3|...|a_n ------------------- 1 |2 |3 |...|n Into a n by 2 table like the…
Daniel Vaca
  • 159
  • 2
  • 3
  • 15
4
votes
4 answers

fetch distinct column values in a single row

This is the data: id name period data1 data2 =================================================== 111 name1 monthly aaaaa bbbbb 111 name1 quaterly ccccc ddddd 111 name1 halfYearly eeeee fffff 111 name1…
puneet2289
  • 65
  • 1
  • 2
  • 10
4
votes
1 answer

Any .Net function or linq query to unpivot data

Is there any .net library available to unpivot excel data? I am currently using LinqToExcel framework to read data from spreadsheets, so not sure if there are dynamic linq queries available to perform the unpivot. Thanks for any suggestions. BTW, I…
user320587
  • 1,347
  • 7
  • 29
  • 57
1 2 3
…
90 91