CODEWITHSUNDEEP
sqloracle-databaseoracle10g
TechGuy
TechGuy

Reputation: 4570

Oracle 10g : Unpivot Column Names and Values

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 Script

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 Script

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

Answers (1)

XING
XING

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

Related Questions