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
Unpivot Multiple Columns in Oracle SQL
Do the cell merging in your application code.
For new_value, try this:
SELECT EMP_NAME
, EMP_NUMBER
, Details
, current_value
, new_value
FROM (SELECT EMP_NAME
, EMP_NUMBER
, PAYROLL_NAME
, cast(PAYROLL_ID as varchar2(100)) PAYROLL_ID
, JOB_TITLE
, cast(JOB_TITLE_ID as varchar2(100)) JOB_TITLE_ID
, LOCATION
, cast(LOCATION_ID as varchar2(100)) LOCATION_ID
, NEW_PAYROLL_NAME
, cast(NEW_PAYROLL_ID as varchar2(100)) NEW_PAYROLL_ID
, NEW_JOB_TITLE
, cast(NEW_JOB_TITLE_ID as varchar2(100)) NEW_JOB_TITLE_ID
, NEW_LOCATION
, cast(NEW_LOCATION_ID as varchar2(100)) NEW_LOCATION_ID
FROM dummy_x)
unpivot ((current_value, new_value) for Details in (
(PAYROLL_NAME, NEW_PAYROLL_NAME) as 'PAYROLL_NAME'
, (PAYROLL_ID , NEW_PAYROLL_ID ) as 'PAYROLL_ID'
, (JOB_TITLE , NEW_JOB_TITLE ) as 'JOB_TITLE'
, (JOB_TITLE_ID, NEW_JOB_TITLE_ID) as 'JOB_TITLE_ID'
, (LOCATION , NEW_LOCATION ) as 'LOCATION'
, (LOCATION_ID , NEW_LOCATION_ID) as 'LOCATION_ID'
)
);
Columns With Multiple Unpivot or For
Do you even need to use UNPIVOT?
SELECT id,
prod_name_reuse AS value1,
'PROD_NAME_REUSE' AS col1,
prod_name_transfer AS value2,
'PROD_NAME_TRANSFER' AS col2
FROM test_unpivot
WHERE id = 120
UNION ALL
SELECT id,
prod_cat_reuse AS value1,
'PROD_CAT_REUSE' AS col1,
prod_cat_transfer AS value2,
'PROD_CAT_TRANSFER' AS col2
FROM test_unpivot
WHERE id = 120;
Update:
SELECT *
FROM (
SELECT u.*,
REGEXP_SUBSTR( type, '_(.+?)_', 1, 1, NULL, 1 ) AS namecat,
REGEXP_SUBSTR( type, '[^_]+$' ) AS reusetransfer
FROM testunpivot
UNPIVOT (
value
FOR type IN (
prod_name_reuse,
prod_cat_reuse,
prod_name_transfer,
prod_cat_transfer
)
) u
)
PIVOT (
MAX( value ) AS value,
MAX( type ) AS col
FOR reusetransfer IN ( 'REUSE', 'TRANSFER' )
);
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 10g : Unpivot Column Names and Values
You can use UNPIVOT in oracle 11g and use the below query to achieve your requirement in Oracle 10g or below:
select 'GL_NEWS_LOCAL_BRD' As COLUMN_NAME,GL_NEWS_LOCAL_BRD as NEWSCOUNT
from TBL_NEWS
union all
select 'GL_GRD_GLOBAL_BRD' as COLUMN_NAME ,GL_GRD_GLOBAL_BRD as NEWSCOUNT
from TBL_NEWSOr if you have oracle 11g and upper verison you can use:
select COLUMN_NAME,NEWSCOUNT
from TBL_NEWS
unpivot( NEWSCOUNT
for COLUMN_NAME in (GL_NEWS_LOCAL_BRD ,GL_GRD_GLOBAL_BRD));Unpivot table with multiple columns and dynamic column names
You just keep unpivoting
SELECT ProductId,ProductName, Substring(col1,1,3) as type, GrossRevenue, DirectCost, NetRevenue
FROM (
SELECT * FROM #tmpProducts
) as t
UNPIVOT ( GrossRevenue for col1 IN (B2B_GrossRevenue,B2C_GrossRevenue)) AS unpvt
unpivot ( DirectCost for col2 in (B2b_DirectCost, B2c_DirectCost)) up2
unpivot ( NetRevenue for col3 in (B2b_NetRevenue, B2c_NetRevenue)) up3
where SUBSTRING(col1,1,3)=SUBSTRING(col2,1,3)
and SUBSTRING(col1,1,3)=SUBSTRING(col3,1,3)and join on the col columns to filter out mismatches
Transpose using column names in Oracle SQL
Are you looking for something like this?
SELECT regexp_substr(company_prev_curr, '(.*?_){1}(.*?)_', 1, 1,'', 2) AS company
,year
,due_ct
,due_amt
,regexp_substr(company_prev_curr, '(.*?_){2}(.*?)_', 1, 1,'', 2) AS prev_curr
FROM test
unpivot(
(due_ct,due_amt)
FOR company_prev_curr IN (
(Comp_A_prev_yr_due_ct,Comp_A_prev_yr_due_amt),
(Comp_A_curr_yr_due_ct,Comp_A_Curr_yr_due_amt)
)
) u;Demo on db<>fiddle
Related Topics
How to Replace a Substring of a String Before a Specific Character
How to Concat Multiple Rows into One Column in SQL Server
Error While Uploading a Report
Replacing Variable Length String with Some Word
Check That a List Parameter Is Null in a Spring Data JPA Query
How to Find Elements in an Array in Bigquery
How to Insert Distinct Records from Table a to Table B (Both Tables Have Same Structure)
Efficiently Querying a Huge Time Series Table for One Row Every 15 Minutes
How to Add Minutes to The Time Part of Datetime
Orm or Something to Handle SQL Tables with an Order Column Efficiently
Recommended Method to Import a .Csv File into Microsoft SQL Server 2008 R2
Oracle Text Escaping with Curly Braces and Wildcards
Sql Order by a Column from Another Table
Why Does This Oracle Drop Column Alter The Default Value of Another Column