Oracle 11g: Unpivot multiple columns and include column name
Just select idnum, sk, f, e, h, 'F'||SK as col_name ... You need to specify all columns instead of an asterix.
Like this http://sqlfiddle.com/#!4/12446/21
Oracle SQL - Unpivot kind of logic but without hard coding
Use UNPIVOT:
SELECT *
FROM table_name
UNPIVOT (
(string, amount) FOR type IN (
(debit_string, debit_amount ) AS 'D',
(credit_string, credit_amount) AS 'C'
)
);
Which, for the sample data:
CREATE TABLE table_name (Debit_String, Credit_String, Debit_Amount, Credit_Amount) AS
SELECT 'ING1', 'ING2', 123, 0 FROM DUAL UNION ALL
SELECT 'INT2', 'INT5', 234, 0 FROM DUAL;
Outputs:
TYPE STRING AMOUNT D ING1 123 C ING2 0 D INT2 234 C INT5 0
Oracle 11g - Unpivot
Use this query:
with t (Dat, Year, Month, Day, Turn_1, Turn_2, Turn_3) as (
select sysdate, 2014, 08, 28, 'Foo', 'Bar', 'Xab' from dual
)
select dat, year, month, day, turn, source from t
unpivot (
source for turn in (Turn_1, Turn_2, Turn_3)
)
DAT YEAR MONTH DAY TURN SOURCE
----------------------------------------------
08/01/2014 2014 8 28 TURN_1 Foo
08/01/2014 2014 8 28 TURN_2 Bar
08/01/2014 2014 8 28 TURN_3 XabUnpivot coumns into rows - PL/ SQL
You have to use Union all to include the duplicate:
Demo
Result:
SELECT ID, A_1 A, B_1 B FROM TABLE1
UNION ALL
SELECT ID, A_2, B_2 FROM TABLE1
UNION ALL
SELECT ID, A_3, B_3 FROM TABLE1 ORDER BY ID;Oracle 11g unpivot very slow - low performance
One thing to try is to use
WITHclause for thebasequery.Instead of
SELECT * from (SELECT my_data, ...) UNPIVOT (...)Try
WITH base as (select my_data, ...) select * from base UNPIVOT (...)In my case it reduced query time by a factor of 10.
pivot/unpivot oracle sql query with more than 2 resulting columns
You can unpivot multiple columns:
...
UNPIVOT INCLUDE NULLS (
(value1, startdate)
FOR code in (
(Math, MathStartDate) as 'Math',
(Science, ScienceStartDate) as 'Science',
(Comp, CompStartDate) as 'Comp',
(Hist, HistStartDate) as 'Hist'
)
);CODE | VALUE1 | STARTDATE
------- | ------ | ---------
Math | 12 | 12-NOV-20
Science | 13 | 02-NOV-20
Comp | 6 | 01-NOV-20
Hist | 45 | 01-NOV-20db<>fiddle
Related Topics
Conversion Failed When Converting from a Character String to Uniqueidentifier Error in SQL Server
Stored Procedure Failing on a Specific User
What Does a Caret (^) Do in a SQL Query
Average Difference Between Two Dates, Grouped by a Third Field
SQL Server Copying Tables from One Database to Another
How to Do Forward Fill as a Pl/Pgsql Function
Insert Blank Row Between Groups of Rows and Sorted by Id in SQL
How to Extend the Query to Add 0 in the Cell When No Activity Is Performed
How to Pass Column Name as Parameter in Select Statement SQL Server
Sql: Difference Between Two Dates
Querying and Selecting Specific Column in SQLalchemy
How to Load Text Data to Database in Postgresql
Row Num Is Not Displaying Any Rows When Using Between Keyword
How to Use Dynamic Column Names in an Update or Select Statement in a Function
Fixing Holes/Gaps in Numbers Generated by Postgres Sequence
Ddmmyyyy to SQL Datetime in SQL
How to Extract Multiple Strings from Single Rows in SQL Server
