CODEWITHSUNDEEP
sqloracle-databaseunpivot
TechGuy
TechGuy

Reputation: 4570

Table transform to to another way Oracle

ID UserId G_NEWS L_NEWS R_NEWS

 1  210   90     160     99
 2  111   89     120     76

I want to transfer table like this(Each and every time i want one User's records only.),

 Column_names Values

 G_NEWS       90  
 L_NEWS       160  
 R_NEWS       99

By using following query i can retrieve the column names but how can i get that value of the specific user.

 SELECT COLUMN_NAME 
 FROM ALL_TAB_COLUMNS 
 WHERE TABLE_NAME='TBL_NEWS';

Upvotes: 0

Views: 47

Answers (2)

are
are

Reputation: 2615

it sounds as unpivot see sample doc here: unpivot sample if columns can be changed dynamically - it can be dynamic sql that provides list of the columns

 with matrix as(
 select
   1 as ID
  ,210 as UserId
  ,90 as G_NEWS
  ,160 as L_NEWS
  ,99 as R_NEWS
 from dual
 union all
 select
   2 as ID
  ,111 as UserId
  ,89 as G_NEWS
  ,120 as L_NEWS
  ,76 as R_NEWS
  from dual)   
select column_name, val from matrix
unpivot
(
  val
    for column_name in ("G_NEWS","L_NEWS","R_NEWS")
)
 where userId = 111
order by userId, column_name

result of test query for user 111:
1   G_NEWS  89
2   L_NEWS  120
3   R_NEWS  76

Upvotes: 1

BriteSponge
BriteSponge

Reputation: 1054

The simple way is as per the comment above;

create table test_table
(id number,userid number,g_news number, l_news number, r_news number)
/
insert into test_table
VALUES(1,210,90,160,99)
/
insert into test_table
VALUES(2,211,89,120,76)
/
commit
/

SELECT 'G_NEWS' AS column_name ,g_news AS col_values FROM test_table WHERE id = 1
union all
SELECT 'L_NEWS',l_news FROM test_table WHERE id = 1
union all
SELECT 'R_NEWS',r_news FROM test_table WHERE id = 1

And the result;

COLUMN COL_VALUES
------ ----------
G_NEWS         90
L_NEWS        160
R_NEWS         99

Upvotes: 0

Related Questions