Reputation: 4570
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),
GL_NEWS_LOCAL_BRD NUMBER(38),
GL_GRD_GLOBAL_BRD NUMBER(38)
)
INSERT INTO TBL_NEWS (
NEWS_ID, STAFF_ID,
MODIFIED_USER,GL_NEWS_LOCAL_BRD,GL_GRD_GLOBAL_BRD)
VALUES (1 ,100,System,50,40);
Finally,My Result table needs to looks like this,Allways i need 1 users record
COLUMN_NAME NEWSCOUNT
GL_NEWS_LOCAL_BRD 50
GL_GRD_GLOBAL_BRD 40
I Tried to take that,By using following query i can take the Column Names.
SELECT COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME='TBL_NEWS' AND COLUMN_NAME LIKE ('GL_%')
Please help me to do this.
Upvotes: 0
Views: 2697
Reputation: 9886
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));
Upvotes: 0