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
-1
votes
1 answer

Transform columns to rows with a twist

I have a table that is formatted like this: SKU | Variant 1 | Variant 2 | Variant 3 0001 | Blue | Red | Green 0002 | Black | white | Yellow What I want to achieve is something like this bellow, basically displaying each variant from above on a…
Calahonda
  • 25
  • 4
-1
votes
2 answers

PIVOT/UNPIVOT more than one column

Is there a way to use SQL Pivot/Unpivot to produce the output under Converted below from the table Original?
VTISCHUK
  • 27
  • 4
-1
votes
1 answer

PostgreSQL 9.6 crosstab, create pivot

How in PostgreSQL 9.6, from a table "import" with structure below in the link... create a query / function, then transpose it to something like this: Unfortunately, table 'import' has no ID field. I tried using crosstab with tablefunc, but no…
p2018
  • 13
  • 1
-1
votes
2 answers

Case statement in a un pivot scenario sql

I'm trying to un pivot a query set as below Member ID N1code N2code N3code Name 1234 234 567 878 ted 23344 4242 23232 Mike I want a case statement of some kind that helps show the type after…
k Drogon
  • 1
-1
votes
1 answer

Unpivot in SQL Server /Tableau for converting columns to rows

I have a survey data where the questions are column names are [Question1], [Question2], [Question3] and response is the row value. The ResponseID is unique and has one row per table.I wanted to unpivot the data where…
user1046415
  • 779
  • 4
  • 23
  • 43
-1
votes
1 answer

How do I convert those columns to rows?

after convert, it should be month 1 2 3 4 5 6 7 8 9 10 11 12 planqty 0 0 6230 0 0 0 0 0 0 0 0 0 actqty 0 0 2631 0 0 0 0 0 0 0 0 0 I will be so appreciated if you can help.
Kevin Lan
  • 33
  • 3
-1
votes
1 answer

SQL for 100 columns, 2 Values For Each Column

I came up with below script:- SELECT * FROM ( SELECT [COST UNIT], [GL ACCOUNT]+ ' ' + [GL] AS GLDetails, [ITEM CODE], [DESCRIPTION], PACKAGING, [U/MEA.], [STD COST PRICE], MAX([STOCK QTY]) AS STOCKQTY, MAX([CLOSING BALANCE]) AS…
Afida A.
  • 9
  • 2
-1
votes
1 answer

Unpivot monthly plan data

How can I transform this input Product | CC | 2017_11 | 2017_12 Product X | 220 | 100 | 200 into something like this? Product | CC | Month |EUR Product X | 220 | 2017_11 | 100 Product X | 220 | 2017_12 | 200 I tried it with UNPIVOT but was not…
stefan
  • 115
  • 1
  • 4
  • 14
-1
votes
1 answer

How Dynamicaly columns in UNPIVOT operator

I currently have the following query: WITH History AS ( SELECT kz.*, kz.__$operation AS operation, map.tran_begin_time as beginT, map.tran_end_time as endT FROM…
user6408649
  • 1,227
  • 3
  • 16
  • 40
-1
votes
1 answer

Is it possible "unpivot" table with SQL?

I have a table: id amount Y2016 Y2017 Y2018 1 100 1 1 2 200 3 300 2 4 400 2 3 5 500 1 and need to unpivot it and made some calculation Result table (multiply…
A1exandr Belan
  • 4,442
  • 3
  • 26
  • 48
-1
votes
4 answers

transform columns to rows

I have a table table1 like below +----+------+------+------+------+------+ | id | loc | val1 | val2 | val3 | val4 | +----+------+------+------+------+------+ | 1 | loc1 | 10 | 190 | null | 20 | | 2 | loc2 | 20 | null | 10 | 10…
user3206440
  • 4,749
  • 15
  • 75
  • 132
-1
votes
2 answers

Unpivot in Access SQL

Hi guys I'm trying to use unpivot in SQL on MS Access and I found the following code online: SELECT CustomerID, Phone FROM ( SELECT CustomerID, Phone1, Phone2, Phone3 FROM dbo.CustomerPhones ) AS cp UNPIVOT ( Phone FOR Phones IN (Phone1,…
Joy
  • 5
  • 3
-1
votes
1 answer

Aggregation of data on 2 columns using pivot and stuff

Have been trying to achieve using Stuff and Pivot in the dynamic Query. I end up getting the unwanted cells either in the Row/column. Input Output
Srinivas
  • 3
  • 3
-1
votes
2 answers

SQL transpose data

I need to transpose data that looks like the following. I don't need to use any aggregate functions, just want to transpose columns to rows. Current view: Name | Code1 | Code2 | Code3 | Pct1 | Pct2 | Pct3 | Amt1 | Amt2 | Amt3 Name1 123 124 …
DPA83
  • 9
  • 2
-1
votes
2 answers

Dynamically construct static columns using pivot in SQL Server

I have a master table which doesn't contain these columns (Visit_date, M-1, M-2, M-3) but in end result I need these columns (Visit_date, M-1, M-2, M-3) along with existing master table columns such as Retailer_id, survey_id, Group_Id, survery_Name…
Pradeep
  • 189
  • 1
  • 5
  • 16
1 2 3
…
90 91