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

Convert column into the rows

This is my current result set of my query: Question Sol25A Sol25B Sol25C Sol40A Sol40B ====================================================== A 1 4 2 6 0 B 2 3 2 1 …
Sanjay Singh
  • 11
  • 1
-2
votes
3 answers

If a column is not null then convert into a row for multiple columns

I have a table like below where I looking to transpose col4 col5 and col6 into rows but in a specific pattern that I listed below col1 col2 col3 col4 col5 col6 a b c 500 200 w x y 1000 300 z g h 200 600 I want to convert it to the…
msv
  • 15
  • 3
-2
votes
1 answer

Excel Code to duplicate rows based on multiple values and columns

i have list of products with sales numbers for each month. i want to create new table which duplicate the values based on month and to write the month in additional column. This is the table and this is the result i want Thank you
M S
  • 1
  • 1
-2
votes
1 answer

How to pivot a multiple columns into single column using VBA?

Initial Hi all, how can I convert the initial Excel table to the final table via VBA (on a separate sheet)? Final
Melvin Ong
  • 1
-2
votes
3 answers

SQL converting two address column into one

I have a table In result I am expecting only two columns Name and address ,for add1 and aad2 if they are similar i want to only show them once in address and for different aad1 and add2 I want to show them twice in two different rows I am expecting…
shubham
  • 3
  • 1
-2
votes
1 answer

Counting values within the same row in SQL Server

My SQL Server problem is as follows: let's say we have clients and we wish to rate them based on 4 categories (Score_1...Score_4) on a scale of 0 to 2. I have a table presented below: What I want my code to do is count the number of 0, 1, and 2…
Herokid92
  • 5
  • 2
-2
votes
1 answer

unpivot issue with multiple like columns

I have a CSV import file that I have no control over concerning the column names or format. The headers consist of id,[temperature range 1],[temperature range 2],[temperature range 3],[temperature range],[temperature range 5],[temperature range…
Travis Irby
  • 25
  • 1
-2
votes
3 answers

SQL Query Convert columns to rows

select sum(cases) as [Total cases], sum(deaths) as [Total deaths] FROM [myschema].[metrics] Can we get by pivot/unpivot function?
Suresh Kasu
  • 13
  • 4
-2
votes
2 answers

SQL Server 2008 R2 split one row of results into two rows

In simple terms the table (t1) looks like this: id hours dollars ----------------------- abc 4 40 I'd like to get results from the table that looks like this: abcHours 4 0 abcDollars 0 40 Thanks
Green
  • 87
  • 7
-2
votes
1 answer

How to I Pivot this table?

I have created a table that looks something like this: I want to Pivot this table such that it looks like; I have tried some other posts about PIVOTs but I could not understand how to do it with more than 2 columns, nor when the values inside the…
Clifford Piehl
  • 483
  • 1
  • 4
  • 11
-2
votes
2 answers

How to get the counts/frequencies of values in a table?

I have the problem that I need to get the number of occurences / the counts or frequencies of values in a table. However, I cannot give the categories before, so in opposite to countif I cannot specify a specific value, as this needs to be done from…
Stat Tistician
  • 813
  • 5
  • 17
  • 45
-2
votes
2 answers

how to unpivot in pandas for 2 level arguments

I have a dataframe that looks like the following image: my df How in pandas unpivot this data, that look like image down? finish data I try do it with df.unstack(), but nothing
A.G.A
  • 1
-2
votes
1 answer

How to transpose columns in Excel by unpivoting the header row

I have an excel file with about 100 columns, each one with multiple entries. The header of each column is the location for all the items in that section. I am trying to transpose this data into a two column excel, the first the item # and the second…
Danconia
  • 543
  • 2
  • 12
  • 28
-2
votes
2 answers

how to use Pivot SQL

For Example, select A,B,C,D,E,YEAR FROM t1 where t1.year = 2018 UNION ALL select A,B,C,D,E,YEAR FROM t2 where t2.year = 2017 execute like this A --- B----C----D----E----YEAR 2 --- 4----6----8----10---2018 1 --- 3----5----7----9----2017 I would…
SASPYTHON
  • 1,571
  • 3
  • 14
  • 30
-2
votes
2 answers

Unpivot the data with an incremental dates

Hi I've the data in the below format Original Data +------+---------+----------+-----------+---------+---------+---------+---------+ | Item | Channel | Location | STARTDATE | PERIOD1 | PERIOD2 | PERIOD3 | PERIOD4…
user6777467
  • 1
  • 1
1 2 3
…
90 91