Home » SQL & PL/SQL » SQL & PL/SQL » Data in to single row (take your time I'm in no rush)
Data in to single row (take your time I'm in no rush) [message #238024] Wed, 16 May 2007 06:49 Go to next message
kian9i
Messages: 1
Registered: November 2006
Location: Delhi
Junior Member
Hi all,

I have a table
abcd (a Char(1))

It contains

a
----
A
B
D
A
C
D
A

I need a sql query that i will get out put count of values
A is 3 times in data
B is 1 time in data
C is 1 time
D is 2 times

a1 b1 c1 d1 e1 f1 g1 h1
---------------------------------
A | 3 | B | 1 | C | 1 | D | 2

Can we do that
Please help

[Updated on: Thu, 31 May 2007 13:11] by Moderator

Report message to a moderator

Re: Data in to single row (very urgent) [message #238027 is a reply to message #238024] Wed, 16 May 2007 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Nothing is urgent in forum
2/ Except reading How to format your posts and other sickies before posting
3/ And searching before posting
4/ And posting your Oracle version (4 decimals)
5/ And posting what you already tried and what is blocking you

I help you: keywords are count and pivot.

Regards
Michel
Re: Data in to single row (very urgent) [message #238038 is a reply to message #238027] Wed, 16 May 2007 07:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Wed, 16 May 2007 13:53

2/ Except reading How to format your posts and other sickies before posting

lol
Re: Data in to single row (very urgent) [message #238039 is a reply to message #238038] Wed, 16 May 2007 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

subconsciously deliberate mistake. ./fa/1587/0/


Re: Data in to single row (very urgent) [message #238040 is a reply to message #238024] Wed, 16 May 2007 07:39 Go to previous messageGo to next message
ShivrajGutte
Messages: 21
Registered: April 2007
Location: PUNE
Junior Member
select (select nm||'|'||count(1) from b_tmp where nm='A' group by nm)||'|'||(select nm||'|'||count(1) from b_tmp where nm='B' group by nm)||'|'||(select nm||'|'||count(1) from b_tmp where nm='C' group by nm) from dual
Re: Data in to single row (very urgent) [message #238048 is a reply to message #238040] Wed, 16 May 2007 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is this garbage?
Format your post!

Regards
Michel
Re: Data in to single row (very urgent) [message #238092 is a reply to message #238024] Wed, 16 May 2007 12:59 Go to previous messageGo to next message
oracleapps_sunilkumar
Messages: 1
Registered: May 2007
Location: Hyderabad
Junior Member
SELECT a||' IS '||COUNT(*)||' TIMES' "DETAILS" FROM abcd
GROUP BY a
HAVING a IN (SELECT DISTINCT(a) FROM abcd)
ORDER BY a
Re: Data in to single row (very urgent) [message #238113 is a reply to message #238092] Wed, 16 May 2007 14:06 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Nice, except that the following line, as far as I can see, doesn't add anything:
HAVING a IN (SELECT DISTINCT(a) FROM abcd) 
Re: Data in to single row (very urgent) [message #238114 is a reply to message #238113] Wed, 16 May 2007 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the overall query does not solve the problem as it requires ONE row for all groups and not a row PER group.

Regards
Michel
Re: Data in to single row (very urgent) [message #238353 is a reply to message #238024] Thu, 17 May 2007 09:46 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
If you know the maximum number of distinct column A values your query will ever return and it is a manageable number then the solution below works. It assumes no more than 5 distinct values, but you can easily extend it to 10, 20, whatever.

create table abcd ( a char(1) );

insert into abcd values ( 'A' );
insert into abcd values ( 'B' );
insert into abcd values ( 'D' );
insert into abcd values ( 'A' );
insert into abcd values ( 'C' );
insert into abcd values ( 'D' );
insert into abcd values ( 'A' );

column result format a40

select result
from
(
  select
    position ,
    a || '|' || a_count ||
    lead( '|' || a || '|' || a_count, 1 ) over ( order by a ) ||
    lead( '|' || a || '|' || a_count, 2 ) over ( order by a ) ||
    lead( '|' || a || '|' || a_count, 3 ) over ( order by a ) ||
    lead( '|' || a || '|' || a_count, 4 ) over ( order by a ) as result
  from
    ( select a
      ,      row_number() over ( order by a ) as position
      ,      to_char(count(a)) a_count
      from   abcd
      group  by a
    )
)
where position = 1 ;

RESULT
----------------------------------------
A|3|B|1|C|1|D|2



If you have an indefinite number of values then check out the eight other string aggregation techniques I describe at SQL Snippets: SQL Techniques Tutorials - Rows to String.

--
Joe Fuda
http://www.sqlsnippets.com/
Re: Data in to single row (very urgent) [message #239005 is a reply to message #238024] Mon, 21 May 2007 03:40 Go to previous messageGo to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member

select MAX('A') A,
       sum(decode(a, 'A', 1, 0)) A_COUNT,
       MAX('B') B,
       sum(decode(a, 'B', 1, 0)) B_COUNT,
       MAX('C') C,
       sum(decode(a, 'C', 1, 0)) C_COUNT,
       MAX('D') D,
       sum(decode(a, 'D', 1, 0)) D_COUNT
  from abcd;


Re: Data in to single row (very urgent) [message #241862 is a reply to message #239005] Wed, 30 May 2007 14:36 Go to previous messageGo to next message
hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE
Senior Member
pravin3032 wrote on Mon, 21 May 2007 03:40

select MAX('A') A,
       sum(decode(a, 'A', 1, 0)) A_COUNT,
       MAX('B') B,
       sum(decode(a, 'B', 1, 0)) B_COUNT,
       MAX('C') C,
       sum(decode(a, 'C', 1, 0)) C_COUNT,
       MAX('D') D,
       sum(decode(a, 'D', 1, 0)) D_COUNT
  from abcd;





a lil bit modified...


select MAX('A')||'|'||
sum(decode(a, 'A', 1, 0))||'|'||
MAX('B')||'|'||
sum(decode(a, 'B', 1, 0))||'|'||
MAX('C')||'|'||
sum(decode(a, 'C', 1, 0))||'|'||
MAX('D')||'|'||
sum(decode(a, 'D', 1, 0)) my_data
from abcd;

This will give u the desired result IF your data is limited to A, B,C and D,

If not then it is better you make a PL/SQL function that will give you your output.

- Hemangi
Re: Data in to single row (very urgent) [message #241863 is a reply to message #241862] Wed, 30 May 2007 14:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If not then it is better you make a PL/SQL function that will give you your output

Are you sure? This is based on what?

Regards
Michel
Re: Data in to single row (very urgent) [message #241976 is a reply to message #238024] Thu, 31 May 2007 04:58 Go to previous messageGo to next message
hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE
Senior Member
Your data:

Table Name: abcd

a
----
A
B
D
A
C
D
A


Function:
CREATE OR REPLACE FUNCTION get_single_line_data() RETURN VARCHAR2 IS
   CURSOR c_data IS
      SELECT a, COUNT(*) acnt FROM abcd GROUP BY a ORDER BY a;
   v_data VARCHAR2(4000);      
BEGIN
   FOR i IN c_data LOOP
      v_data := v_data||' '||i.a||' | '||i.a_cnt||' |';
   END LOOP;
   v_data := RTRIM(v_data, ' |');
   RETURN v_data;
END;


Create this function at DB.

After this write this SQL at prompt:

SELECT get_single_line_data() FROM dual;

it will return the desired results.
In this scenario it does not matter how many diferent vales you have in the column "a".

enjoy Cool

[Updated on: Thu, 31 May 2007 04:59]

Report message to a moderator

Re: Data in to single row (very urgent) [message #241986 is a reply to message #241976] Thu, 31 May 2007 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I did mean it is not possible.
You said it is better to do it in PL/SQL.
Are you sure? Prove it.

Regards
Michel
Re: Data in to single row (very urgent) [message #241990 is a reply to message #238024] Thu, 31 May 2007 05:31 Go to previous messageGo to next message
hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE
Senior Member
i have ...

the code is given. refer to the post before your challenge to prove.
Re: Data in to single row (very urgent) [message #241993 is a reply to message #241990] Thu, 31 May 2007 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hmm, you mean you don't know how to do it in SQL?

Regards
Michel
Re: Data in to single row (very urgent) [message #241994 is a reply to message #238024] Thu, 31 May 2007 05:34 Go to previous messageGo to next message
hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE
Senior Member
also please refer to the line in the first post...

Quote:
This will give u the desired result IF your data is limited to A, B,C and D,

If not then it is better you make a PL/SQL function that will give you your output.


This means that the single SQL will give him the output if his output is to be restricted to A B C and D.

Once his data grows to E, the SQL will have to be updated to add one more DECODE line.

The PL/SQL function given above will take care of this scenario, irrespective of how much data he has... Scalability is the essence of the code.

ENJOY Cool
Re: Data in to single row (very urgent) [message #241995 is a reply to message #238024] Thu, 31 May 2007 05:36 Go to previous messageGo to next message
hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE
Senior Member
hey ... mikey,

I am always open to suggestions. If this can be done in a sigle query and will give me the kind of scalability that the PL/SQL function can... bring it on baby.
Always open to learning new things and i enjoy forums more when ppl give better suggestions.

no hard feelings.
ENJOY Cool
Re: Data in to single row (very urgent) [message #242000 is a reply to message #241995] Thu, 31 May 2007 05:55 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
In my opinion it's also more readable if you have a function in the select than to have a multi-line decode/concat every time you need it.

And if you use the same logic in more than one place it's also easier to maintain one function than multiple sqls.

It may be a little slower performance wise, but that is something that can only be considered on a case by case basis.

But I always tend to prefer the scalability/readability of code over performance, until there is an actual problem with the performance.
Re: Data in to single row (very urgent) [message #242007 is a reply to message #238024] Thu, 31 May 2007 06:07 Go to previous messageGo to next message
hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE
Senior Member
Personnaly if you ask me.. i would not prefer to give output in the format asked by kian cause as thomas has pointed out... this will cause performance issues in future, even if it is a single SQL query.
Re: Data in to single row (very urgent) [message #242019 is a reply to message #241994] Thu, 31 May 2007 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Scalability is the essence of the code

Not of your code.
You should use BULK COLLECT instead of a row by row fetch.

Regards
Michel
Re: Data in to single row (very urgent) [message #242074 is a reply to message #238024] Thu, 31 May 2007 09:34 Go to previous messageGo to next message
hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE
Senior Member
Gimme an example. modify the code that i have given or gimme new one.
Re: Data in to single row (very urgent) [message #242135 is a reply to message #242074] Thu, 31 May 2007 14:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's what can be do.
We'll compare it with your function.
I had to slighty modify it as it does not compile as it.
I also created a much bigger table to make suitable performances tests.
I named your function get_single_line_data_1 and the other one get_single_line_data_2.
SQL> create table abcd 
  2  as select substr(object_name,1,1) a from dba_objects
  3  /

Table created.

SQL> CREATE OR REPLACE FUNCTION get_single_line_data_1 RETURN VARCHAR2 IS
  2     CURSOR c_data IS
  3        SELECT a, COUNT(*) acnt FROM abcd GROUP BY a ORDER BY a;
  4     v_data VARCHAR2(4000);      
  5  BEGIN
  6     FOR i IN c_data LOOP
  7        v_data := v_data||i.a||'|'||i.acnt||'|';
  8     END LOOP;
  9     v_data := RTRIM(v_data, '|');
 10     RETURN v_data;
 11  END;
 12  /

Function created.

SQL> CREATE OR REPLACE FUNCTION get_single_line_data_2 RETURN VARCHAR2 IS
  2     type string_array is table of varchar2(20) index by binary_integer;
  3     v_sa string_array;
  4     v_data VARCHAR2(4000);      
  5  BEGIN
  6     SELECT a||'|'||COUNT(*) acnt 
  7     BULK COLLECT INTO v_sa
  8     FROM abcd GROUP BY a ORDER BY a;
  9     FOR i IN 1..v_sa.count LOOP
 10        v_data := v_data||v_sa(i)||'|';
 11     END LOOP;
 12     v_data := RTRIM(v_data,'|');
 13     RETURN v_data;
 14  END;
 15  /

Function created.

Let's see the result:
SQL> SELECT get_single_line_data_1 result FROM dual;
RESULT
----------------------------------------------------------------------------------------------------
/|11367|A|552|B|5|C|58|D|1411|E|427|F|9|G|542|H|207|I|289|J|26|K|344|L|519|M|72|N|13|O|166|P|87|Q|3|
R|219|S|322|T|45|U|504|V|544|W|6|X|61|_|29|c|94|j|2600|m|10|o|1793|s|2263

1 row selected.

SQL> SELECT get_single_line_data_2 result FROM dual;
RESULT
----------------------------------------------------------------------------------------------------
/|11367|A|552|B|5|C|58|D|1411|E|427|F|9|G|542|H|207|I|289|J|26|K|344|L|519|M|72|N|13|O|166|P|87|Q|3|
R|219|S|322|T|45|U|504|V|544|W|6|X|61|_|29|c|94|j|2600|m|10|o|1793|s|2263

1 row selected.

Same one, good.
Now let's take some very small performances measures: execute each one 1000 times:
SQL> set timing on
SQL> declare
  2    result varchar2(4000);
  3  begin
  4    for i in 1..1000 loop
  5      SELECT get_single_line_data_1 into result FROM dual;
  6    end loop;
  7  end;
  8  / 

PL/SQL procedure successfully completed.

Elapsed: 00:00:16.09
SQL> declare
  2    result varchar2(4000);
  3  begin
  4    for i in 1..1000 loop
  5      SELECT get_single_line_data_2 into result FROM dual;
  6    end loop;
  7  end;
  8  / 

PL/SQL procedure successfully completed.

Elapsed: 00:00:15.12

6% not so bad and the difference will be much greater when numbers of values of A increase (here only 31 distinct A).

Now let's see in SQL:
SQL> select replace(substr(sys_connect_by_path(acnt,'|'),2),'#','|') result
  2  from (
  3  select a||'#'||count(*) acnt,
  4         row_number() over (order by a) rn,
  5         count(*) over () cnt
  6  from abcd 
  7  group by a
  8  )
  9  where rn = cnt
 10  connect by prior rn = rn-1
 11  start with rn = 1
 12  /
RESULT
----------------------------------------------------------------------------------------------------
/|11367|A|552|B|5|C|58|D|1411|E|427|F|9|G|542|H|207|I|289|J|26|K|344|L|519|M|72|N|13|O|166|P|87|Q|3|
R|219|S|322|T|45|U|504|V|544|W|6|X|61|_|29|c|94|j|2600|m|10|o|1793|s|2263

1 row selected.

Yes analytics rock... but what about the performances? Same test:
SQL> set timing on
SQL> declare
  2    result varchar2(4000);
  3  begin
  4    for i in 1..1000 loop
  5      select replace(substr(sys_connect_by_path(acnt,'|'),2),'#','|') 
  6      into result
  7      from (
  8      select a||'#'||count(*) acnt,
  9             row_number() over (order by a) rn,
 10             count(*) over () cnt
 11      from abcd 
 12      group by a
 13      )
 14      where rn = cnt
 15      connect by prior rn = rn-1
 16      start with rn = 1;
 17    end loop;
 18  end;
 19  / 

PL/SQL procedure successfully completed.

Elapsed: 00:00:30.78

Rather bad.
Actually "connect by" duration is something like exponential with number of values of A.
With only 4 as in the OP example it is far better than function but when the number increases it comes worst and worst.
I didn't check when it is slower than the function, maybe around 15, but it depends on many things: environment - it requires memory and my laptop is always swapping, query, cpu power, concurrency - I was alone, and so on.

Regards
Michel
Re: Data in to single row (take your time I'm in no rush) [message #242155 is a reply to message #238024] Thu, 31 May 2007 15:55 Go to previous messageGo to next message
hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE
Senior Member
if there is an index on the abcd table it will further improve performance even with 1000 times more records.

It wont make much of a difference... BULK COLLECT and the normal function, when there are only 2 columns, when the data to be fetched increases, it makes a difference. and yes michel in that case it will work faster. For now the data is simple enough for a simple solution.
Re: Data in to single row (take your time I'm in no rush) [message #242329 is a reply to message #242155] Fri, 01 June 2007 10:47 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
You guys might be interested in the performance + scalability tests I did on various rows to string techniques. The results are at SQL Snippets: Rows to String - Performance Comparison Charts.

Hemavb, you mentioned "if this can be done in a sigle query and will give me the kind of scalability that the PL/SQL function can... bring it on baby". You might like some of the MODEL based solutions presented in the same section as the tests (there are links to them on the page referenced above). They let you do string aggregation in a single query and scale better than function based solutions like STRAGG or the object oriented approach I tested.

--
Joe Fuda
SQL Snippets
Re: Data in to single row (take your time I'm in no rush) [message #242330 is a reply to message #242155] Fri, 01 June 2007 10:48 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if there is an index on the abcd table it will further improve performance even with 1000 times more records

This is why I stop the analysis at this step saying:
Quote:
it depends on many things:...

Quote:
For now the data is simple enough for a simple solution.

In this case SQL is faster but, imo, example is not reality and it is better to give a solution or at least the elements for real world. And in real world not using BULK operation is now an error (although 10g does it now for you behind the scene).

Regards
Michel
Previous Topic: Need to Tune this query
Next Topic: rowid mystry
Goto Forum:
  


Current Time: Sat Dec 03 09:48:28 CST 2016

Total time taken to generate the page: 0.08030 seconds