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
3 answers

How to UnPivot for multiple columns SQLServer

In my application, I have used store product description values as follows: ID BILLNO CUS_NAME DATE TOT_BAL S1 S2 S3 S4 D1 D2 D3 D4 Q1 Q2 Q3 Q4 U1 U2 U3 U4 T1 T2 T3 T4 TOTAL …
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
3
votes
4 answers

Include NULL values in unpivot

I have been looking for a solution for this problem for quite a long time. But, couldn't find any. I have a table as below: Month Col_1 Col_2 Col_3 Col_4 Col_5 --------------------------------------------- Jan NULL NULL 1 …
user2538559
  • 59
  • 2
  • 9
3
votes
1 answer

Transpose rows into columns in SQL without using aggregate functions like MAX, AVG etc

I have a table like this: CREATE TABLE MyDataTable ( [RollNo] varchar(8), [QuesBlock] int, [RespVal] varchar(2) ); INSERT INTO MyDataTable ([RollNo], [QuesBlock], [RespVal]) VALUES ('MBA0001', 1, A), ('MBA0001', 2, B), ('MBA0001',…
ducktorcoder
  • 43
  • 2
  • 7
3
votes
1 answer

Transposing only few columns in SQL Server

I have 4 columns in my table like : key cusi isi name 1 46644UAQ1 US46642EAV83 A 1 46644UAR9 XS0062104145 A 1 254206AC9 A 2…
baiduXiu
  • 167
  • 1
  • 3
  • 15
3
votes
1 answer

Column as row concatenated with another column

So I have to use a database which has time as column (there's a column for every 10 min in a specific day), the id in here isn't a primary key even though it's unique (and a primary key) in another table. And the rows equals to a day. So I'd like to…
Jérôme B
  • 311
  • 5
  • 18
3
votes
3 answers

SQL Server Pivot using multiple sets of columns

From a table like this: CREATE TABLE dbo.mytable ( [ID] int, [Category] INT, [Lh] varchar(30), [Sev] INT, [Risk] INT ) insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (5, 2, 'Impossible', 4,…
Alex
  • 55
  • 5
3
votes
2 answers

SQL server how to convert column values into rows

I will try to explain my question. My table structure: name Code_1 Code_1_value Code_2 Code_2_value Code_3 Code_3_value N1 ABC1 10 ABC2 15 ABC3 6 N2 ABC1 3 NULL NULL BAA1 10 N3 …
Klapsius
  • 3,273
  • 6
  • 33
  • 56
3
votes
1 answer

Convert multiple value columns into new key, value pairs

I need to convert a .csv file's contents from: continent, region, country, 1990, 1991, 1992, 1993, ... Africa, East Africa, Ethiopia, 12, 14, 15, 9, ... to: continent, region, country, year, value Africa, East Africa, Ethiopia, 1990, 12 Africa,…
John Verrone
  • 307
  • 3
  • 16
3
votes
6 answers

Unpivot an Excel matrix/pivot-table?

Is there a quick way to "unpivot" an Excel matrix/pivot-table (in Excel or elsewhere), without writing macros or other code ? Again, I can write code (C# or VBA or whatever) that does that myselfs. I want to know if it is possible to do it without…
Stefan Steiger
  • 78,642
  • 66
  • 377
  • 442
3
votes
3 answers

Union of multiple columns as one column

Could you please advise what would be the best way to create a union column for 12 separate columns (located in 12 different Excel sheets within a workbook) with or without VBA? There are good manuals how to do it for two columns without VBA (using…
Stan
  • 41
  • 1
  • 1
  • 2
3
votes
1 answer

Syntax errors with UNPIVOT and JOIN in FROM clause

I'm using SQL Server Express (with advanced services), version 10.50.1600.1. I need to use a UNPIVOT to create multiple rows from a single ROW, and also need to use JOINS, to get fields from master data tables. I can make 2 working but separate…
Rafael
  • 91
  • 1
  • 1
  • 9
3
votes
2 answers

Count number of values across multiple columns

I have a table with 11 columns. The first column includes the category names. The remaining 10 columns have values like white, green, big, damaged etc. and these values can change in time. I need a SQL query to find how many are there in table (in…
güven
  • 33
  • 1
  • 5
3
votes
5 answers

How to unpivot a table in PostgreSQL

I am having difficulties writing a Postgres function, as I am not familiar with it. I have multiple tables to import into Postgres with this format: id | 1960 | 1961 | 1962 | 1963 | ... ____________________________________ 1 23 45 87 …
luftikus143
  • 1,285
  • 3
  • 27
  • 52
3
votes
2 answers

Unpivot Multiple Columns in MySQL

I've got some non-normalized data that I'm trying to unpivot the columns on, I'm hoping you all can help me figure out the best way to do this. I've done this using multiple union statements, but what I'm hoping to do is create a dynamic query that…
masdawg
  • 33
  • 1
  • 3
3
votes
1 answer

tSQL UNPIVOT of comma concatenated column into multiple rows

I have a table that has a value column. The value could be one value or it could be multiple values separated with a comma: id | assess_id | question_key | item_value ---+-----------+--------------+----------- 1 | 859 | Cust_A_1 | …
Sam Carleton
  • 1,339
  • 7
  • 23
  • 45
1 2 3
…
90 91