Home » SQL & PL/SQL » SQL & PL/SQL » split result set into column (merged 6) (oracle 10g)
split result set into column (merged 6) [message #563814] Fri, 17 August 2012 05:53 Go to next message
umeshchandraseth
Messages: 60
Registered: June 2011
Location: new delhi
Member
Hi All,
i have a table emp with three column
columns are (empid varchar,empnomini varchar,nominitype varchar),
data in table like
empid empnomini nominitype
1 x B
1 y c
2 xx B
2 yyyy c


and i want data comes like

empid nominitype b nominitype c
1 x y
2 xx yyyy

please help me,

thanks in advance.
Re: split result set into column [message #563822 is a reply to message #563814] Fri, 17 August 2012 06:06 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
SQL> with emp as (
  2               select 1 empid,'x' empnomini,'B' nominitype from dual union all
  3               select 1,'y','c' from dual union all
  4               select 2,'xx','B' from dual union all
  5               select 2,'yyyy','c' from dual
  6              )
  7  select  empid,
  8          max(case nominitype when 'B' then empnomini end) nominitype_b,
  9          max(case nominitype when 'c' then empnomini end) nominitype_c
 10    from  emp
 11    group by empid
 12  /

     EMPID NOMI NOMI
---------- ---- ----
         1 x    y
         2 xx   yyyy

SQL> 


And if you are on 11g, use PIVOT.

SY.
Re: split result set into column [message #563825 is a reply to message #563822] Fri, 17 August 2012 06:15 Go to previous messageGo to next message
umeshchandraseth
Messages: 60
Registered: June 2011
Location: new delhi
Member
thanks for reply sir,
first thing it is just example of data,
i have milion of data in particular table,
so please provide me specific code for split resultset into column.
Regards
Umesh Chandra
Re: split result set into column [message #563828 is a reply to message #563825] Fri, 17 August 2012 06:29 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
select  empid,
        max(case nominitype when 'B' then empnomini end) nominitype_b,
        max(case nominitype when 'c' then empnomini end) nominitype_c
  from  emp
  group by empid
/


SY.
Re: split result set into column [message #563832 is a reply to message #563828] Fri, 17 August 2012 06:37 Go to previous messageGo to next message
umeshchandraseth
Messages: 60
Registered: June 2011
Location: new delhi
Member
its working sir
but tell me one more thing,
if data is like that
empid empnomini nominitype
1 x B
1 y C
1 z C


we dont know how many nomini their in particular column on the basis of nominitype reason behind we have milions
of data in table,
so how can we show it on the screen
suppose
empid 1 with nominitype c have 4 empnomini then result split into 4 column ,if 5 then result will split in 5 column.
thanks


Re: split result set into column [message #563853 is a reply to message #563832] Fri, 17 August 2012 10:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, SY, how now?
In addition, answer the next question OP will post as there is surely something he did not yet say.

Regards
Michel
Re: split result set into column [message #563892 is a reply to message #563832] Fri, 17 August 2012 12:51 Go to previous message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
In SQL list of columns is static. So you must know upfront all possible values for nominitype:

select  empid,
        max(case nominitype when 'B' then empnomini end) nominitype_b,
        max(case nominitype when 'c' then empnomini end) nominitype_c,
        .
        .
        .
        max(case nominitype when 'whatever' then empnomini end) nominitype_whatever
  from  emp
  group by empid
/



SY.
Previous Topic: Get a specific record in duplicates
Next Topic: Procedure Performance
Goto Forum:
  


Current Time: Wed Oct 01 12:44:15 CDT 2014

Total time taken to generate the page: 0.04574 seconds