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

Unpivot multiple columns in Snowflake

I have a table that looks as follows: I need to unpivot the Rating and the Comments as follows: What is the best way to do this in Snowflake? Note: there are some cells in the comment columns that are NULL Adding details: create or replace table…
Saqib Ali
  • 3,953
  • 10
  • 55
  • 100
5
votes
3 answers

Unpivot table in PostgreSQL

I have the below table as a result of SUM(Case....End). Account Product_A Product_B Product_C 101 1000 2000 3000 102 2000 1000 …
Haitham
  • 51
  • 2
5
votes
1 answer

how can I turn column into rows in presto?

I want to turn column into a new rows and save the values. For example: BEFORE NAME COMDEDY HORROR ROMANCE brian 10 20 14 tom 20 10 11 AFTER NAME GANRE RATING brian comedy 10 brian horror 20 brian …
nowheretogo
  • 125
  • 1
  • 5
5
votes
3 answers

Convert columns data into rows in PostgreSQL

I have data in the following format. order_no rate jan feb mar .... 1 1200 2 4 2 1000 1 5 3 2400 14 3 Now I want to…
kamal
  • 65
  • 1
  • 6
5
votes
1 answer

Unpivot in Potgresql

How can I unpivot in Postgresql without using UNION? I have more than 100 columns, and I am looking for a neat way to do it. Given table: id c1 c2 c3 1 X Y Z 2 A B C 3 Y C Z Desired…
geek2000
  • 451
  • 5
  • 18
5
votes
2 answers

Change table to tall format using panda (UNPIVOT)

I have a table like this user company company2 company3 company4 1 Mac Lenovo Hp null 2 Mac MSI Sony And using pandas I would like it to be …
Aurora
  • 282
  • 3
  • 16
5
votes
2 answers

Turn SQLite columns to rows

I am trying to convert a query which returns a single row with multiple columns to multiple rows with one or multiple columns. As an example my query looks like the following visualized: SELECT _id, value1, value2, value3, value4 FROM table WHERE…
Adrian Stratienco
  • 93
  • 1
  • 7
5
votes
2 answers

Hive - Unpivot functionality in hive

I have two table as follows: Table A userid | code | code_name | property_id 0001 | 1 | apple_id | Y1234 0031 | 4 | mango_id | G4567 0008 | 3 | grape_id | H1209 00013 | 2 | peach_id | Z5643 Table 2 apple_id | mango_id |…
activelearner
  • 7,055
  • 20
  • 53
  • 94
5
votes
1 answer

How to update columns in unpivot query my Query attached Sql Server?

i am trying to add column in my query it is as under, SELECT Name as [holiday_name] FROM tableMonth t UNPIVOT (ID for Name in (m1,m2,m3,sf1,sf2)) u WHERE ID != 0.0 and D_No ='0700' this query was running fine but when i add "sf1" and "sf2" it give…
user3664724
  • 425
  • 1
  • 6
  • 18
5
votes
1 answer

Unpivot ALL Columns in a SQL Table

I have a table with 30 columns and I want to easily unpivot ALL columns. I understand I can use this strategy: SELECT col, value INTO New_Table FROM (SELECT * FROM Test_Data) p UNPIVOT (value FOR col IN (Column_Name1, Column_Name2...…
user3347843
  • 71
  • 1
  • 5
5
votes
2 answers

Pivot multiple columns into rows in SQL Server?

I have a table with data like this create table temp ( colName varchar(50), name varchar(50), icon varchar(150), totalcount int ) insert into temp values ('Eng1', 'Following', 'followingicon.png', 1564) insert into temp values…
Lac Ho
  • 217
  • 4
  • 15
5
votes
2 answers

how to count horizontal values on a database?

assuming that I have a db that have an horizontal structure like this: ID | NAME | DATA1 | DATA2 | DATA3 | DATA4 | DATA5 | DATA6 | DATA7 1 | mmm | 0 | 1 | 0 | 3 | 5 | 1 | 0 2 | bbb | 0 | 0 | 0 | 1 | 0 …
Matteo Bononi 'peorthyr'
  • 2,170
  • 8
  • 46
  • 95
5
votes
1 answer

SQL query for fetching a single record in format "column heading: column value"

Suppose I have this table: Create table test(a int, b int, c int, d int) I can write simply 'select * from test' to get the following first record: A B C D 1 2 3 4 But instead, I want it like this (Four rows for single…
teenup
  • 7,459
  • 13
  • 63
  • 122
4
votes
3 answers

How to use pd.melt to unpivot a dataframe where columns share a prefix?

I'm trying to unpivot my data using pd.melt but no success so far. Each row is a business, and the data contains information about the business and multiple reviews. I want my data to have every review as a row. My first 150 columns are in groups of…
raz
  • 43
  • 4
4
votes
1 answer

Snowflake unpivoting

I need to transpose a table in which column1 is name of an entity and column2 to column366 are dates in a year that hold a dollar amount. The table, the select statement and the output result are all given below - Question - This syntax requires…
HSahay
  • 41
  • 2
1 2 3
…
90 91