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

Stored procedure to unpivot

I have a table with these columns: surname name birthdate id_doc role 01 02 ... 50 Vardy Jack 19870215 1234 'emp' 20200110 20200527 20200610 Where fields from 01 to 50 are dates. I need to have a table like…
marko
  • 487
  • 1
  • 6
  • 15
2
votes
2 answers

Transform Columns to Rows dynamically using T-SQL

I have the following table where I need to transform Columns to Rows tbl_Survey: SurveyID Quest_1 Quest_2 Quest_3 7 1 1 1 8 2 2 2 9 3 3 …
Hell-1931
  • 489
  • 1
  • 6
  • 24
2
votes
1 answer

Unpivot in spark-sql/Scala column names are numbers

I have tried the built in stack function described in this post Unpivot in spark-sql/pyspark for Scala, and works fine for each of the columns identified with a code that contains a letter but not in those columns where the code is just a number. I…
galiani
  • 25
  • 4
2
votes
3 answers

Is there a more efficient way to write my filters in this specific context?

I am using SQL Server 2014 and I have a table (t1) in my database which contains a list of numbers (n1 to n6). An extract is given below: Id n1 n2 n3 n4 n5 n6 100 3 10 26 31 35 39 101 1 3 11 22 36 40 102 10 19…
user3115933
  • 4,303
  • 15
  • 54
  • 94
2
votes
1 answer

SQL - Audit two tables - Pulling singular column for errors

I have a series of tables I have to audit against each other. Each table has a varying amount of columns anywhere from 4 to 40. The task at hand is to audit column ID in tableA and column ID in table B. etc. my code looks like this SELECT …
YelizavetaYR
  • 1,611
  • 6
  • 21
  • 37
2
votes
2 answers

How can I unpivot three types of values (including the column headers)

The data I have looks like this: ╔═════════╦═════════╦═════════╦══════════════╦══════════════╦══════════════╗ ║ option1 ║ option2 ║ option3 ║ percent_opt1 ║ percent_opt2 ║ percent_opt3…
hanbanan
  • 81
  • 5
2
votes
1 answer

How to count how many times a specific value appeared on each columns and group by range

I'm new on postgres and I have a question: I have a table with 100 columns. I need to count the values from each columns and count how many times they appeared, so I can group then based on the range that they fit I have a table like this(100…
Breno1982
  • 45
  • 4
2
votes
1 answer

how to split a pandas dataframe from wide to tall shape

I have a dataframe containing this structure and I have figured out how to 'unpivot' the df by doing this, but I am pretty sure is not the more pythonic way I would like to have. Can you please suggest a better way to do it?: v = [[{'BIN_ID_WDM': i,…
user_dhrn
  • 557
  • 1
  • 5
  • 18
2
votes
1 answer

mysql - sums per months/years for every row per year

I have the following demo Demo page CREATE TABLE `tblappointment` ( `app_id` mediumint(8) UNSIGNED NOT NULL, `app_date` date NOT NULL, `work_id` smallint(5) UNSIGNED NOT NULL, `app_price` double DEFAULT NULL, `app_price_paid` double…
PeterPan2020
  • 174
  • 1
  • 12
2
votes
3 answers

How to find the column with maximum value within a single row with multiple columns?

I have a temporary table as follows: |----------|------------|-------------|--------|-----------| | Country | Confirmed | Unconfirmed | Deaths | Recovered | |----------|------------|-------------|--------|-----------| | A | 95782 | …
learningsql
  • 19
  • 1
  • 8
2
votes
2 answers

Pivot or Unpivot a small table

I'm having a hard time wrapping my head around pivot/unpivot - all examples I find online I think are more complex than I need. Imagine a table as such: CREATE TABLE Custom (ID tinyint identity, value nvarchar(20)) INSERT INTO Custom VALUES…
gemArt
  • 35
  • 6
2
votes
1 answer

Mysql merge count multiple column

I have a table(tb_data) which like this +---------+---------------------+---------------------+---------------------+---------------------+ | Disease | Additional_Disease1 | Additional_Disease2 | Additional_Disease3 | Additional_Disease4…
Kanien Notiros
  • 27
  • 4
2
votes
1 answer

Pandas: ungroup and melt space-indented records

I'm a novice in python&pandas, could you please advise me if it is possible to ungroup and unpivot such dataframe? The groups in source data are in the only column marked by prefix spaces and look like import pandas as pd import numpy df =…
evesq
  • 21
  • 2
2
votes
2 answers

How to pivot a table in Oracle PLSQL?

I have a table given as below: KEY 1995 1996 1997 1998 1999 2000 2001 2002 2003 123 0 0 0 461 1188 2049 1056 377 295 And I want the data to be visible as given below. KEY SEQ_NBR SEQ_YR …
Yash Sharma
  • 324
  • 6
  • 25
2
votes
2 answers

Oracle 11 SQL : Is there a way to split 1 row into x rows

Customer asked to Split 1 row from the Oracle DB SQL into 6 rows. Let's say, originally the SQL (complex sql with multiple joins , etc) is pulling in 9 columns: select A, B, C, D, E, F, G, H, I from X, Y, Z . . . (but quite complex query) 1) A,…
anjanb
  • 12,999
  • 18
  • 77
  • 106
1 2 3
…
90 91