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

Using UNPIVOT with Dynamic SQL

I have a table with this schema: tblResults Question1 | Question2 | Question3 | etc | etc | Question240 | In these columns values can be of the following: 1, 2, 3, 4, N, M I need to present the data like the this: | QuestionNumber | 1 | 2 | 3 | 4…
Jack Pettinger
  • 2,715
  • 1
  • 23
  • 37
-1
votes
2 answers

Can anyone write query for this?

My table is as below recordId fwildcardId refNumber wildcardName wildcardValue comments 404450 154834 2 aaa p p 404450 154833 1 aa oi p …
Kuntady Nithesh
  • 11,371
  • 20
  • 63
  • 86
-1
votes
2 answers

Sql Column to Row joined tables

I have got two tables like that . --------------------------------------------------- | AltID | Alan 1| Alan 2 | Alan 3 | UserId | --------------------------------------------------- | 1 | Opel | null | Engine | 5121 | | 2 …
cooolstaff
  • 75
  • 1
  • 9
-1
votes
1 answer

Pivot with multiple colums and rows

Could you help me to achieve the following I have this SQL output table DateWeek Keep_1 This_1 Order_1 Keep_2 This_2 Order_2 Keep_1-Keep_2 This_1-This_2 Order_1-Order_2 1/1/2013 9 8 7 6 5 4 3 3 …
Selrac
  • 2,203
  • 9
  • 41
  • 84
-1
votes
1 answer

sql/php - display records horizontally and vertically

So I have this data from basic mySQL select query, that outputs per row. -Basic mySQL select…
nublet
  • 23
  • 2
-1
votes
2 answers

How to calculate visits by month if customers has assigned days of visits

i have this table customer sunday monday tuesday wedesneday thursday friday saturday 1 0 0 1 1 1 0 0 2 0 1 0 0 0 0 1 3 0 0 1 0 …
angel
  • 4,474
  • 12
  • 57
  • 89
-2
votes
1 answer

write a MYSQL query to optimize the following query?

write a MYSQL query to optimize the following query ? the emp_attendance table look like empid,name,month,01,02,03,......31. and its values are 121,robin,jan-2023,P,P,SL,LOP,......upto 31 days. i want result like…
vinayak
  • 7
  • 4
-2
votes
1 answer

I want to apply unpivot function in SQL Server and Sqllite

I have below table Monday Tuesday Wednesday Thursday Friday WeekStartsOn 8 8 8 8 8 2019-09-06 I want to convert it to below…
Sourav Guha
  • 1
  • 2
-2
votes
1 answer

How to pivot or unpivot this table to achieve this specific output?

I am using SQL Server 2014 and I have the following Table (T1): ID HOTEL ADDRESS COORDINATES DESCRIPTION VALUE A001 A Royal Road 20.5 GP FA MARKINGS 2 A001 A Royal Road 20.5 …
user3115933
  • 4,303
  • 15
  • 54
  • 94
-2
votes
1 answer

Select data from tables when needed columns are stored as records in a different table

An app is developed where a user picks what data he wants to see in a report. Having data…
We8
  • 1
  • 4
-2
votes
1 answer

unpivot list : data type error in SQL SERVER

I'm trying to UNPIVOT some columns in the table but having trouble with some of the columns as the datatype is different. So I tried to CAST all columns to VARCHAR(255) but it still doesn't work. It gives error that: Conversion failed when…
Richi
  • 9
  • 5
-2
votes
2 answers

sql data change

Currently my data looks like this: A 15902 8.11 9.20 7 8 5 6 A 15902 2021 8.11 7 5 A 15902 2022 9.20 8 6 I'm quite unsure how to do this.…
brockey2sockey
  • 1
  • 4
-2
votes
2 answers

How to transpose columns to rows and get the max and min value in Oracle

Input Student_Name Maths Science Social A 20 30 10 B 10 20 30 Output Student_Name max_sub min_sub A Science Social B Social 20Maths I tried select student_name,max(marks) m1, min(marks) m2 from ( select student_name,…
Martin
  • 1
  • 3
-2
votes
2 answers

How to change column values to rows in Access db

I have a table generated by a query in Access as something like this-- Id length Height Diameter code 1-X(C1) 1 4 100 S W 1-Y(C2) 3 7 200 N which I want to change to…
user18568746
  • 1
  • 1
-2
votes
2 answers

T-SQL query table to re-sort data

I am trying to come up with a means of translating the following…
HotelLima
  • 11
1 2 3
…
90 91