I have a table and the associated values.I need to Unpivot that table and take columns to rows with Column names.
In my scenario GL_ Columns Dynamically grow.
CREATE TABLE TBL_NEWS
NEWS_ID VARCHAR2(50 BYTE) NOT NULL,
STAFF_ID VARCHAR2(50 BYTE),
MODIFIED_USER VARCHAR2(50 BYTE),
INSERT INTO TBL_NEWS (
VALUES (1 ,100,System,50,40);
Finally,My Result table needs to looks like this,Allways i need 1 users record
WHERE TABLE_NAME='TBL_NEWS' AND COLUMN_NAME LIKE ('GL_%')
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_NEWS
Or 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));