TechGuy TechGuy - 8 days ago 6
SQL Question

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.

Answer

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));