Home » SQL & PL/SQL » SQL & PL/SQL » Column To Row Generation Help Required (Oracle 11gR2, 11.2.0.1.0, RHEL 5.0)
Column To Row Generation Help Required [message #590442] Thu, 18 July 2013 05:29 Go to next message
jimit_shaili
Messages: 224
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Friends,

Here i've a case of, For single test there are multiple chemist's assigned to it.
I want a result for each chemist assigned how many test.

I want the output like below.

CREATE TABLE SAM.TEMP
(
  ID          NUMBER(4),
  CHEMIST_ID  VARCHAR2(30)
)
/

Insert into TEMP   (ID, CHEMIST_ID) Values   (1, '12,15,16,17');
Insert into TEMP   (ID, CHEMIST_ID) Values   (2, '4,10,9,12');
Insert into TEMP   (ID, CHEMIST_ID) Values   (3, '12,10,4,6');
Insert into TEMP   (ID, CHEMIST_ID) Values   (4, '15,16');

commit;


Regards

Jimit
Re: Column To Row Generation Help Required [message #590445 is a reply to message #590442] Thu, 18 July 2013 05:34 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Hi,

SQL> SELECT regexp_count('12,15,16,17', ',') + 1 number_of_tests FROM dual;

NUMBER_OF_TESTS
---------------
	      4

SQL> 



Please read regexp_count.

Regards,
Dariyoosh
Re: Column To Row Generation Help Required [message #590448 is a reply to message #590445] Thu, 18 July 2013 05:39 Go to previous messageGo to next message
jimit_shaili
Messages: 224
Registered: June 2006
Location: India, Ahmedabad
Senior Member
sorry friends i forget to out put of desired result, which is as below.


CHEMIST_ID		TEST_CNT
----------              --------
4			2
6			1
9			1
10			2
12			3
15			2
16			2
17			1



Regards

Jimit
Re: Column To Row Generation Help Required [message #590449 is a reply to message #590448] Thu, 18 July 2013 05:44 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Ok, then you should have posted it at the beginning, because the solution that I gave you obviously is not for this problem. Take a look at row generator. In this topic based on your oracle version you can choose a solution allowing to split those comma delimited values into separate rows. Once this has been done, you just aggregate (count) based on groups of chemist_id

Regards,
Dariyoosh

[Updated on: Thu, 18 July 2013 05:48]

Report message to a moderator

Re: Column To Row Generation Help Required [message #590451 is a reply to message #590449] Thu, 18 July 2013 05:50 Go to previous messageGo to next message
pablolee
Messages: 2615
Registered: May 2007
Location: Scotland
Senior Member
I'm sure that there will be much tidier solutions out there, and this is just a very rough example, but it should hopefully give you an idea of what you can do.
WITH t AS (SELECT 1 ID, ',1,12,322,4,' val FROM dual)
SELECT val
     , substr(val,pos+1, pos2-(pos+1))
FROM (SELECT val
           , regexp_instr(val,',',1,LEVEL) pos 
           , regexp_instr(val,',',1,LEVEL+1) pos2
      FROM t
      CONNECT BY LEVEL <= 4);
Re: Column To Row Generation Help Required [message #590460 is a reply to message #590449] Thu, 18 July 2013 06:02 Go to previous messageGo to next message
jimit_shaili
Messages: 224
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Thanks Dariyoosh,

My problem has been solved by your suggestion. Here is the solution.

select to_number(chemistId),count(*) from
(
	select id testId
	--,column_value
	,regexp_substr(chemist_id, '[^,]+', 1, column_value) chemistId
	from temp,
	table(cast(multiset(select level from dual connect by level <= regexp_count(chemist_id,',')+1) as sys.odciNumberList))
) group by chemistId	
order by 1

   CHEMIST   TEST_CNT
---------- ----------
         4          2
         6          1
         9          1
        10          2
        12          3
        15          2
        16          2
        17          1



Re: Column To Row Generation Help Required [message #590462 is a reply to message #590451] Thu, 18 July 2013 06:07 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
pablolee wrote on Thu, 18 July 2013 12:50
I'm sure that there will be much tidier solutions out there, and this is just a very rough example, but it should hopefully give you an idea of what you can do.
WITH t AS (SELECT 1 ID, ',1,12,322,4,' val FROM dual)
SELECT val
     , substr(val,pos+1, pos2-(pos+1))
FROM (SELECT val
           , regexp_instr(val,',',1,LEVEL) pos 
           , regexp_instr(val,',',1,LEVEL+1) pos2
      FROM t
      CONNECT BY LEVEL <= 4);


This will work only if your table has one row, for two or more rows you will need in fact a row generator.

Regards,
Dariyoosh
Re: Column To Row Generation Help Required [message #590466 is a reply to message #590462] Thu, 18 July 2013 06:23 Go to previous messageGo to next message
pablolee
Messages: 2615
Registered: May 2007
Location: Scotland
Senior Member
You must have missed the bit where I said that this was a very rough example and that there would be tidier solutions. It's right there at the start of my post Wink
Re: Column To Row Generation Help Required [message #590472 is a reply to message #590466] Thu, 18 July 2013 07:09 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Oh, yes, I definitely didn't pay enough attention to that part of your comment Shocked , I apologize Smile

Regards,
Dariyoosh
Re: Column To Row Generation Help Required [message #590475 is a reply to message #590472] Thu, 18 July 2013 07:35 Go to previous message
pablolee
Messages: 2615
Registered: May 2007
Location: Scotland
Senior Member
No need for apologies, I was just having a wee joke mate. Wink
Previous Topic: Removing duplicate in Hierarchy Level
Next Topic: Update SQL
Goto Forum:
  


Current Time: Sat Aug 23 12:28:40 CDT 2014

Total time taken to generate the page: 0.09571 seconds