0

do you know the function or procedure in oracle which would allow me the following:

TABLE Clients
COLUMN   id, name, vlag


columnname  column_value                   
id          1          
name        Jon         
flag        M         


columnname  column_value                   
id          2          
name        Jessica         
flag        F
nAPL
  • 60
  • 5
  • Your question is quite unclear. – Gordon Linoff Oct 14 '16 at 09:54
  • example my table clients with columns id and name. whether there is a function in oracle that would take this format for a given id example select * from clients give a this result 'id name 1 jon 2 jessica' I need the make columns (headers) to rows and columns to print the name of the column - Value column – nAPL Oct 14 '16 at 10:03
  • 2
    @APC - It is called unpivoting not pivoting – Pரதீப் Oct 14 '16 at 10:13
  • 1
    What you're after is a form of transposing called unpivoting. StackOverflow already has lots of questions with answers on this topic. [Check out this search](http://stackoverflow.com/questions/tagged/oracle+unpivot) . Different approaches suit different needs, for instance whether you have fixed table / column projection or a dynamic one. – APC Oct 14 '16 at 10:20
  • @Prdp - quite right, I have posted a revised comment with a new search – APC Oct 14 '16 at 10:20
  • @APC yes this is my answer, but my table is 200 column and this is very hard to manually typing sql :) thank very mach – nAPL Oct 14 '16 at 10:32

2 Answers2

2
with dat as (select '1' as id, 'Jon' as name, 'M' as vlag from dual union all
             select '2' as id, 'Jessica' as name, 'F' as vlag from dual)
select * from dat unpivot (column_value for column_name in (id, name, vlag));

But result will be pretty simmilar to previous answer (if you add there rownum and use as a key).

Kacper
  • 4,798
  • 2
  • 19
  • 34
  • @Kasper yes this is my answer, but my table is 200 column and this is very hard to manually typing sql :) thank very mach – nAPL Oct 14 '16 at 10:33
  • @nAPL try something like that: http://dba.stackexchange.com/questions/48393/passing-column-names-dynamically-to-unpivot – Kacper Oct 14 '16 at 10:44
  • thank this is my solutions http://stackoverflow.com/questions/15100101/unpivot-on-an-indeterminate-number-of-columns – nAPL Oct 14 '16 at 12:30
0

Try this. But I would say this is ineffecient

select  'id' as column_name, id as column_value from table
union all
select 'name',name from table
union all
select 'flag',vlag from table
Madhivanan
  • 13,470
  • 1
  • 24
  • 29