0
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';
TechGuy
  • 4,298
  • 15
  • 56
  • 87
  • Which RDBMS you are using... Is it Oracle, MySQL or Sql Server? – User Nov 23 '16 at 10:21
  • @Srinath this is Oracle – TechGuy Nov 23 '16 at 10:22
  • 2
    You need `unpivot` if you use Oracle 11 http://stackoverflow.com/questions/19124891/oracle-unpivot-columns-to-rows or some pre-unpivot solution like that http://stackoverflow.com/questions/19280591/oracle-pivot-operator if you use pre-11g – Kacper Nov 23 '16 at 10:27
  • Do a UNION ALL, with one SELECT for each x_NEWS column. – jarlh Nov 23 '16 at 10:46

2 Answers2

1

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
are
  • 2,535
  • 2
  • 22
  • 27
  • One limitation of PIVOT/UNPIVOT is that the values for column_name must be know up front. The OP has said in another comment that there may be more %_NEWS columns. If you know how to handle that I would be very interested. – BriteSponge Nov 23 '16 at 17:08
  • see this thread about this http://stackoverflow.com/questions/11881162/unpivot-table-with-multiple-columns-and-dynamic-column-names . 2nd answere there is about dynamic sql – are Nov 24 '16 at 00:42
0

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
BriteSponge
  • 1,034
  • 9
  • 15