Home » SQL & PL/SQL » SQL & PL/SQL » Multi rows convert into one row (Oracle 10g)
Multi rows convert into one row [message #439678] Tue, 19 January 2010 01:10 Go to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Dear Group Members
I want to convert query that return multi rows into one row. Scanario is as under
select * from inout v
where v.empcode = '00047'
and v. dated = '27-dec-09'

EMPCODE	 DATED	   IN_TIME   NET_TIME	ATT_STATUS	VAL
00047	27/12/2009	0	0	  G	        1
00047	27/12/2009	0	0	  R	        1
00047	27/12/2009	7.44	483	  P	        1

This query gives multi rows but i want to convert it in one row like this
EMPCODE	 DATED	   IN_TIME   NET_TIME	ATT_STATUS	VAL
00047	27/12/2009	7.44	483	  R	        1


if employee has come on day that has three attendance status ('G','R','P') then it should show in_time, net_time and attendance status should be 'R';

EMPCODE	 DATED	   IN_TIME   NET_TIME	ATT_STATUS	VAL
00047	25/12/2009	0	0	  R	        1
00047	25/12/2009	7.59	476	  P	        1

Suppose there is rest on 25/12/2009 and employee has come on this day then it shows the following result.
EMPCODE	 DATED	   IN_TIME   NET_TIME	ATT_STATUS	VAL
00047	25/12/2009	7.59	476	  R	        1

It shows 'R' in Att_status coloums instead of 'P'.

Thanks and Regards

Re: Multi rows convert into one row [message #439679 is a reply to message #439678] Tue, 19 January 2010 01:12 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Multi rows convert into one row [message #439683 is a reply to message #439678] Tue, 19 January 2010 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use row_number function to ordered your rows and select the one with number 1.

Here's an example, select the employee with the max salary in each department:
SQL> select empno, ename, deptno, sal
  2  from (select e.*, 
  3               row_number() over (partition by deptno order by sal desc) rn
  4        from emp e)
  5  where rn = 1
  6  /
     EMPNO ENAME          DEPTNO        SAL
---------- ---------- ---------- ----------
      7839 KING               10       5000
      7788 SCOTT              20       3000
      7698 BLAKE              30       2850

3 rows selected.

If you post a working Test case, I workl with your data.

Regards
Michel

[Updated on: Tue, 19 January 2010 01:17]

Report message to a moderator

Re: Multi rows convert into one row [message #439689 is a reply to message #439683] Tue, 19 January 2010 01:49 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Thanks michel
Your answer did not solve my problem. The test case is as under.
CREATE TABLE INOUT
(
  EMPCODE     VARCHAR2(10),
  DATED       DATE,
  IN_TIME     NUMBER(10,2),
  NET_TIME    NUMBER(10,2),
  ATT_STATUS  VARCHAR2(5),
  VAL         NUMBER(3)
)

insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
values           ('00047','25-DEC-2009',	0    ,	  0     ,   'R' ,	      1);
insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
values           ('00047','25-DEC-2009',	7.59 ,	  476   ,   'P' ,	      1);
insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
values           ('00047','27-DEC-2009', 0    ,    0	    ,   'G'	,       1);
insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
values           ('00047','27-DEC-2009',	0    ,	  0     ,	  'R'	,       1);
insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
values           ('00047','27-DEC-2009',	7.44 ,	  483   ,	  'P' ,	      1);


note that
'G' = Gusted
'R' = Rest
'P' = Present

i need the row of that have in_time but atten_status should be other than 'P'. See the first post in detail.

Thanks & Regards
Re: Multi rows convert into one row [message #439696 is a reply to message #439689] Tue, 19 January 2010 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said, I didn't understand clearly your rule, tell us what is the result with the data you provided and why.
Also why ROW_NUMBER does not fit your need?

Regards
Michel
Re: Multi rows convert into one row [message #439698 is a reply to message #439696] Tue, 19 January 2010 02:34 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Dear Michel

I once again describe my problem.

select * from inout v
where v.empcode = '00047'
and v. dated = '27-dec-09'

EMPCODE	 DATED	   IN_TIME   NET_TIME	ATT_STATUS	VAL
00047	27/12/2009	0	0	  G	        1
00047	27/12/2009	0	0	  R	        1
00047	27/12/2009	7.44	483	  P	        1


I neet the query that return the third row because it has in_time and net_time > 0 but the atten_status of third row is 'P' i need the Atten_status should be 'R'. It shows that employee has in on restday. R has priority on 'G' and 'P' is in_time is > 0;

Require result is as under:
the third row and replaces att_status 'P' with 'R'
EMPCODE	 DATED	   IN_TIME   NET_TIME	ATT_STATUS	VAL
00047	27/12/2009	7.44	483	  R	        1


thanks
Re: Multi rows convert into one row [message #439699 is a reply to message #439689] Tue, 19 January 2010 02:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It would have been useful if you could have provided a detailed description of which values of ATT_STATUS take precedence over which others.

Based on the partial information that you have provided, here's a solution that you should be able to adjust to your requirements:
select empcode
      ,dated
      ,max(in_time)  in_time
      ,max(net_time) net_time
      ,substr(max(case when att_status = 'G' then 'XG'
                       when att_status = 'P' then 'ZP'
                       when att_status = 'P' then 'YR'
                  end)
             ,2,1) att_status
      ,max(val) val
from   inout
group by empcode,dated;


Additionally, in your test case, you should wrap the strings that you're using for dates in explicit TO_DATE calls - not everyone has the same default date format as you.
Re: Multi rows convert into one row [message #439897 is a reply to message #439699] Wed, 20 January 2010 00:27 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Dear Members
I did not find the required solution. Waiting for reply.

Thanks and regards.
Re: Multi rows convert into one row [message #439898 is a reply to message #439897] Wed, 20 January 2010 00:34 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
sweetkhaliq wrote on Tue, 19 January 2010 22:27
Dear Members
I did not find the required solution. Waiting for reply.

Thanks and regards.


nobody owes you any answer!
Re: Multi rows convert into one row [message #439901 is a reply to message #439897] Wed, 20 January 2010 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sweetkhaliq wrote on Wed, 20 January 2010 07:27
Dear Members
I did not find the required solution. Waiting for reply.

Thanks and regards.

JRowbottom
It would have been useful if you could have provided a detailed description of which values of ATT_STATUS take precedence over which others.

It seems you have no solution because no one is clearly understanding your requirement.
Explain why JRowbottom's post is not a solution.

Regards
Michel

Re: Multi rows convert into one row [message #439902 is a reply to message #439898] Wed, 20 January 2010 00:59 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Dear Members
Here is test case and require out put from this is as under.
CREATE TABLE INOUT
(
EMPCODE VARCHAR2(10),
DATED DATE,
IN_TIME NUMBER(10,2),
NET_TIME NUMBER(10,2),
ATT_STATUS VARCHAR2(5),
VAL NUMBER(3)
)


insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
values           ('00047','03-DEC-2009',	7.44    ,	  547     ,   'P' ,	      1);
insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
values           ('00047','04-DEC-2009',	0    ,	  0     ,   'HCP' ,	      0.5);
insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
values           ('00047','04-DEC-2009',	9    ,	  350     ,   '1/2' ,	      0.5);
insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
values           ('00047','25-DEC-2009',	0    ,	  0     ,   'R' ,	      1);
insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
values           ('00047','25-DEC-2009',	7.59 ,	  476   ,   'P' ,	      1);
insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
values           ('00047','27-DEC-2009', 0    ,    0	    ,   'G'	,       1);
insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
values           ('00047','27-DEC-2009',	0    ,	  0     ,	  'R'	,       1);
insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
values           ('00047','27-DEC-2009',	7.44 ,	  483   ,	  'P' ,	      1);

Require Out put is
EMPCODE	 DATED	   IN_TIME   NET_TIME	ATT_STATUS	VAL
00047	03/12/2009	7.44	547	  P	         1 
00047	04/12/2009	9	350	HCP              0.5
00047	25/12/2009	7.59	476	  R	        1
00047	27/12/2009	7.44	483	  R	        1


Thanks and regards

[Updated on: Wed, 20 January 2010 01:07]

Report message to a moderator

Re: Multi rows convert into one row [message #439904 is a reply to message #439902] Wed, 20 January 2010 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 20 January 2010 07:59
JRowbottom
It would have been useful if you could have provided a detailed description of which values of ATT_STATUS take precedence over which others.

It seems you have no solution because no one is clearly understanding your requirement.
Explain why JRowbottom's post is not a solution.

Please explain this, repeating the test case (which will be useful) does not make the requirements clearer (and so we unfortunately can't work with the test case).
There you add a new HCP status we don't know and don't know what to do with it.
I promise that as soon as I understand what you want I work on it.

Regards
Michel

[Updated on: Wed, 20 January 2010 01:10]

Report message to a moderator

Re: Multi rows convert into one row [message #439911 is a reply to message #439904] Wed, 20 January 2010 01:39 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Dear Members
in firt case
EMPCODE	 DATED	   IN_TIME   NET_TIME	ATT_STATUS	VAL
00047	03/12/2009	7.44	547	  P	         1 

It should show this row as it is because there is only one row for dated 03/12/2009.

2nd case
EMPCODE	DATED        IN_TIME    NET_TIME  ATT_STATUS	VAL
00047	04/12/2009	0	0	   HCP	        0.5
00047	04/12/2009	9	350	   1/2	        0.5


Her because there are more than one rows against 04/12/2009 so it should take the row that have in_time greater than 0 and att_stats should be HCP that is other than the resulted row.
Same dow with the date 25/12/2009
EMPCODE	 DATED	   IN_TIME   NET_TIME	ATT_STATUS	VAL
00047	25/12/2009	0	0	  R	        1
00047	25/12/2009	7.59	476	  P	        1


3rd case
EMPCODE	 DATED	   IN_TIME   NET_TIME	ATT_STATUS	VAL
00047	27/12/2009	0	0	  G	        1
00047	27/12/2009	0	0	  R	        1
00047	27/12/2009	7.44	483	  P	        1


Here there are three rows against 27/12/2009. so it should take the row that have in_time greater than 0 and att_stats should be other than 'P' that is resulted row.

The final rusult should be as under
EMPCODE	 DATED	   IN_TIME   NET_TIME	ATT_STATUS	VAL
00047	03/12/2009	7.44	547	  P	         1 
00047	04/12/2009	9	350	  HCP	        0.5
00047	25/12/2009	7.59	476	  R	        1
00047	27/12/2009	7.44	483	  R	        1
Thanks and Regards

[Updated on: Wed, 20 January 2010 02:44]

Report message to a moderator

Re: Multi rows convert into one row [message #439928 is a reply to message #439911] Wed, 20 January 2010 04:25 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> CREATE TABLE INOUT
  2  (
  3  EMPCODE VARCHAR2(10),
  4  DATED DATE,
  5  IN_TIME NUMBER(10,2),
  6  NET_TIME NUMBER(10,2),
  7  ATT_STATUS VARCHAR2(5),
  8  VAL NUMBER(3,2)
  9  );

Table created.

SQL> insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
  2  values           ('00047','03-DEC-2009',   7.44    ,         547     ,   'P' ,           1);

1 row created.

SQL> insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
  2  values           ('00047','04-DEC-2009',   0    ,    0     ,   'HCP' ,           0.5);

1 row created.

SQL> insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
  2  values           ('00047','04-DEC-2009',   9    ,    350     ,   '1/2' ,         0.5);

1 row created.

SQL> insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
  2  values           ('00047','25-DEC-2009',   0    ,    0     ,   'R' ,             1);

1 row created.

SQL> insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
  2  values           ('00047','25-DEC-2009',   7.59 ,    476   ,   'P' ,             1);

1 row created.

SQL> insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
  2  values           ('00047','27-DEC-2009', 0    ,    0           ,   'G'     ,       1);

1 row created.

SQL> insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
  2  values           ('00047','27-DEC-2009',   0    ,    0     ,         'R'   ,       1);

1 row created.

SQL> insert into inout(EMPCODE,    DATED     ,IN_TIME , NET_TIME, ATT_STATUS, VAL  )
  2  values           ('00047','27-DEC-2009',   7.44 ,    483   ,         'P' ,       1);

1 row created.

SQL> commit;

Commit complete.

SQL>   select empcode
         ,dated
         ,max(in_time)  in_time
         ,max(net_time) net_time
         ,max(case when att_status = 'G' then 'G'
                          when att_status = 'P' then 'P'
                          when att_status = 'P' then 'R' else att_status
                     end)
                 att_status
        ,max(val) val
  from   inout
  group by empcode,dated
  order by 2;

EMPCODE    DATED        IN_TIME   NET_TIME ATT_S        VAL
---------- --------- ---------- ---------- ----- ----------
00047      03-DEC-09       7.44        547 P              1
00047      04-DEC-09          9        350 HCP           .5
00047      25-DEC-09       7.59        476 R              1
00047      27-DEC-09       7.44        483 R              1


it ` not Jrowbottom`s mistake its yours ...(Table_structure)

Select * from inout;
please execute that.....
whats the value of "val" for stt_status "HCP"

sriram Smile

[Updated on: Wed, 20 January 2010 04:34]

Report message to a moderator

Re: Multi rows convert into one row [message #439931 is a reply to message #439928] Wed, 20 January 2010 04:36 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
thank you moderators for deleting My network errors(Duplicate posts)

sriram Smile
Re: Multi rows convert into one row [message #439949 is a reply to message #439931] Wed, 20 January 2010 05:39 Go to previous message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Thanks ramoradba
For pointing out the column width of val.
Previous Topic: Bypass package function with name conflict
Next Topic: Numeric or value error
Goto Forum:
  


Current Time: Wed Sep 28 17:43:50 CDT 2016

Total time taken to generate the page: 0.25897 seconds