Home » SQL & PL/SQL » SQL & PL/SQL » Sql (oracle 9.2.0.3)
Sql [message #433882] Fri, 04 December 2009 16:48 Go to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
WITH TAB1 AS
(
SELECT 'X' GBN,'A01' ITEM_CD FROM DUAL UNION ALL
SELECT 'X' GBN,'A02' ITEM_CD FROM DUAL UNION ALL
SELECT 'X' GBN,'B02' ITEM_CD FROM DUAL UNION ALL
SELECT 'Y' GBN,'B01' ITEM_CD FROM DUAL UNION ALL
SELECT 'Y' GBN,'B02' ITEM_CD FROM DUAL UNION ALL
SELECT 'Z' GBN,'G01' ITEM_CD FROM DUAL UNION ALL
SELECT 'Z' GBN,'A02' ITEM_CD FROM DUAL UNION ALL
SELECT 'Z' GBN,'B02' ITEM_CD FROM DUAL
)
SELECT * FROM TAB1;


I want a result like this...

GBN      ITEM_CD
X        A01A02B02
Y        B01B02
Z        G01A02B02


How would you do it using SQL (excluding sys_connect_by_path)?

Regards,
Ved

[Updated on: Fri, 04 December 2009 16:59]

Report message to a moderator

Re: Sql [message #433910 is a reply to message #433882] Fri, 04 December 2009 23:32 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
This is a FAQ asked several times EACH week.
Please search BEFORE posting; here search for "pivot" (and you can do it in SQL Reference also).


Regards,
Delna
Re: Sql [message #433916 is a reply to message #433882] Sat, 05 December 2009 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
How would you do it using SQL (excluding sys_connect_by_path)?

Why?

Regards
Michel
Re: Sql [message #433917 is a reply to message #433910] Sat, 05 December 2009 00:56 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Delna,
I believe that you read my post.I want in sql (without using Tom's stragg function/sys_connect_by_path method).

Let me know if you have any alternative.

Thanks
Re: Sql [message #433918 is a reply to message #433916] Sat, 05 December 2009 01:00 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Sir,
I want to know if there is any way or alternative I can say ...
using functions like substr,lpad?
Re: Sql [message #433919 is a reply to message #433917] Sat, 05 December 2009 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
without using Tom's stragg function/sys_connect_by_path method

Now you add stragg to exclusion list and the next post you will add what? All what we will post you?

I help you: use wm_concat.
Anyway, it is a FAQ and (almost) ALL ways have already been posted.

Regards
Michel

[Updated on: Sat, 05 December 2009 01:01]

Report message to a moderator

Re: Sql [message #433920 is a reply to message #433918] Sat, 05 December 2009 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Its_me_ved wrote on Sat, 05 December 2009 08:00
Sir,
I want to know if there is any way or alternative I can say ...
using functions like substr,lpad?

Post all the solutions you don't want.

Regards
Michel

Re: Sql [message #433921 is a reply to message #433919] Sat, 05 December 2009 01:06 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
wm_concat is not a documented function
Re: Sql [message #433922 is a reply to message #433920] Sat, 05 December 2009 01:08 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Michel Cadot wrote on Sat, 05 December 2009 01:01
Its_me_ved wrote on Sat, 05 December 2009 08:00
Sir,
I want to know if there is any way or alternative I can say ...
using functions like substr,lpad?

Post all the solutions you don't want.

Regards
Michel


without using Tom's stragg function/sys_connect_by_path method Smile

Quote:

Now you add stragg to exclusion list and the next post you will add what?

Sir, In my first post mentioned that I want to know if it can be done in sql ...so excluding stragg function

[Updated on: Sat, 05 December 2009 01:23]

Report message to a moderator

Re: Sql [message #433923 is a reply to message #433921] Sat, 05 December 2009 01:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Its_me_ved wrote on Fri, 04 December 2009 23:06
wm_concat is not a documented function

The sun rises in the East.
So what is your point?
Re: Sql [message #433924 is a reply to message #433922] Sat, 05 December 2009 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So wm_concat is a valid solution.
Try with it and post it.

Regards
Michel

Edit: doh! No it is not as you are in 9.2.
But answer BlackSwan and I question: Why? what is your point?

[Updated on: Sat, 05 December 2009 01:17]

Report message to a moderator

Re: Sql [message #433925 is a reply to message #433923] Sat, 05 December 2009 01:38 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

The sun rises in the East.
So what is your point?


Good to know that you know that! Smile But the Sun CAN NOT rise at night!!

You could have seen the version 9.2.0.3.

Quote:

But there are some issues:

1) it may or may not be documented depending upon your release.
2) you must have installed oracle workspace manager for it to be available.

Source:http://www.orafaq.com/node/2290

[Updated on: Sat, 05 December 2009 01:45] by Moderator

Report message to a moderator

Re: Sql [message #433926 is a reply to message #433925] Sat, 05 December 2009 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ You still don't answer to our questions
2/ So you found that OraFAQ (and Kevin) already answered the question
3/ are his solutions valid for you?

Regards
Michel
Re: Sql [message #433927 is a reply to message #433926] Sat, 05 December 2009 01:59 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

There are five basic methods for creating a delimited string in Oracle:


1) (my favorite) use a hierarchical query, also known as the sys_connect_by_path method [Other than this method]
2) create a simple plsql function for your own needs [ I want to do in a sql only]
3) use an oracle supplied function DBMS_UTIL.TABLE_TO_COMMA and WM_CONCAT [ I want to do in a sql only. And I can not use wm_concat]
4) bulid your own user defined aggregate (I first saw this on asktomhome) [ I want to do in a sql only]
5) even use XML (super ugly and not sure why anyone would do it this way so I won't show it)


Ok, Do any one have any sql method other than this to solve the problem?

[Updated on: Sat, 05 December 2009 02:17] by Moderator

Report message to a moderator

Re: Sql [message #433928 is a reply to message #433927] Sat, 05 December 2009 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ Do NOT use code tags when you quote but quote ones (or split the lines)

2/ Why don't you try to find a solution instead of just asking others? Post your tries.

3/ By the way, xPAD, SUBSTR and so on, aren't they functions like SYS_CONNECT_BY_PATH. So why do you allow some and not others? (
I know you will not answer the question, so list ALL the functions you allow.

Regards
Michel

[Updated on: Sat, 05 December 2009 02:22]

Report message to a moderator

Re: Sql [message #433929 is a reply to message #433928] Sat, 05 December 2009 02:32 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

2/ Why don't you try to find a solution instead of just asking others? Post your tries.



Sir, Had I not tried, I wont have ask questions here.May be orafaq dont have solution for this. I would take the answer from this forum as NO Smile

Quote:

3/ By the way, xPAD, SUBSTR and so on, aren't they functions like SYS_CONNECT_BY_PATH. So why do you allow some and not others? (
I know you will not answer the question, so list ALL the functions you allow.

Yes, they are functions like SYS_CONNECT_BY_PATH..I know the use of SYS_CONNECT_BY_PATH. I do not want to use that. I want to see in other way...

Regards,
Ved

[Updated on: Sat, 05 December 2009 02:39]

Report message to a moderator

Re: Sql [message #433933 is a reply to message #433929] Sat, 05 December 2009 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Its_me_ved wrote on Sat, 05 December 2009 09:32
Quote:

3/ By the way, xPAD, SUBSTR and so on, aren't they functions like SYS_CONNECT_BY_PATH. So why do you allow some and not others? (
I know you will not answer the question, so list ALL the functions you allow.

Yes, they are functions like SYS_CONNECT_BY_PATH..I know the use of SYS_CONNECT_BY_PATH. I do not want to use that. I want to see in other way...

Regards,
Ved

Quote:
list ALL the functions you allow.

In case you don't see it I put it in bold red.

Regards
Michem

Re: Sql [message #433934 is a reply to message #433933] Sat, 05 December 2009 03:11 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

There are five basic methods for creating a delimited string in Oracle:


1) (my favorite) use a hierarchical query, also known as the sys_connect_by_path method [Other than this method]
2) create a simple plsql function for your own needs [ I want to do in a sql only]
3) use an oracle supplied function DBMS_UTIL.TABLE_TO_COMMA and WM_CONCAT [ I want to do in a sql only. And I can not use wm_concat]
4) bulid your own user defined aggregate (I first saw this on asktomhome) [ I want to do in a sql only]
5) even use XML (super ugly and not sure why anyone would do it this way so I won't show it)

Anything other than the above 5 mentioned. As I already told, not user defined ones (not the stragg one)


Anything you can use like LPAD,MAX, ROW_NUMBER etc..

[Updated on: Sat, 05 December 2009 03:14]

Report message to a moderator

Re: Sql [message #433935 is a reply to message #433929] Sat, 05 December 2009 03:13 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
The only reason for "I want to achieve X, without the use of Y, which is by far the optimal solution" would be an exercise of the mind & test of skills.
If you fail to find something, asking others for the solution would fail both goals. Besides, asking others without clarifying why you want it is plain weird.
Finally, asking it and using "Sql" as topic title is beyond words.

[Updated on: Sat, 05 December 2009 03:14]

Report message to a moderator

Re: Sql [message #433937 is a reply to message #433935] Sat, 05 December 2009 03:20 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Frank Sir, Please take it as an excercise to the mind.
I do not know where I should post this question.. May be the wrong place. You can move it to TEST /Homework category.As it was a sql question I put it here.

I want to see if there is another approach rather than those mentioned in asktom mostly and other forums.

Quote:

asking others for the solution would fail both goals


Asking others if they have alternatives...I have the solution using sys_connect_by_path..but is there could be an alternative one?

You provided an alternative method in previous message using LAG function instead of first_value(http://www.orafaq.com/forum/t/152839/146068/)?

Can not this one have alternative???



Regards,
Ved

[Updated on: Sat, 05 December 2009 03:26]

Report message to a moderator

Re: Sql [message #433938 is a reply to message #433937] Sat, 05 December 2009 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Please take it as an excercise to the mind.

Quote:
Can not this one have alternative???

Why don't you try and post what you tried?
Otherwise:
Quote:
asking others for the solution would fail both goals

Or do you think you are unable to search the solution of "an excercise to the mind" and are just able to copy what others find?

Regards
Michel
Re: Sql [message #433957 is a reply to message #433938] Sat, 05 December 2009 11:39 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

do you think you are unable to search the solution of "an excercise to the mind" and are just able to copy what others find?



Regards,
Ved

[Updated on: Sat, 05 December 2009 11:45]

Report message to a moderator

Re: Sql [message #433959 is a reply to message #433957] Sat, 05 December 2009 11:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Does this mean you agree?

Regards
Michel
Re: Sql [message #433960 is a reply to message #433959] Sat, 05 December 2009 12:00 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
No its a question from me as well Smile
Re: Sql [message #433961 is a reply to message #433882] Sat, 05 December 2009 14:15 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I'd help you with another alternative, but since I wrote the article that you quoted, it is a good bet I don't know any other way. Hmm... I have been doing oracle for 25 years so if I don't know the solution maybe it does not exist, or it may exist and I did know it at one time and choose to forget it because it was stinky who knows.

Everything in life is about tradeoffs. You pick one thing over another because of what your goals and needs are and what you like and don't like.

I would suggest to you that even if there is a SQL ONLY SOLUTION to your problem, you likely do not want to use it over one of the other MORE TRADITIONAL SOLUTIONS because I figure the sql only solution will have lots of its own faults. This fact actually makes your question a good learning tool because in finding the answer you will be able to evaluate it and learn a lot about what SQL is good at and when it needs to be augmented. So if this is a learning excercise, I applaud whoever invented it.

Good luck in your search. If you get an answer, feel free to attach it to my prior article.

To all others posting here, you can know that he did search, for after all he found the posting I created in OraFAQ for this question. At least VED is putting out some effort on his part.

Good luck, Kevin
Re: Sql [message #434097 is a reply to message #433961] Mon, 07 December 2009 03:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In the hope of puting this topic to rest:

This is the most comprehensive list of techniques I've ever found. It includes the analytic and normal version of the standard limited columns SQL solution, four different ways of doing it with the Model clause, and several others.

I suspect there's a fair chance of @Ved coming back and saying 'Is there an SQL only solution that doesn't use all the other SQL only features and doesn't use the MODEL clause'

If I were being all cynical, I'd say that @Ved's behaviour reminds me of someone trying to get an acknowledged expert in a field to say "I don't know" in the belief that this would somehow increase his own status.
Re: Sql [message #434099 is a reply to message #434097] Mon, 07 December 2009 03:34 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, Kevin already said that he doesn't know, so I add my "I don't know", too. Cool
Re: Sql [message #434440 is a reply to message #433917] Wed, 09 December 2009 05:10 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> select deptno,ename
  2  from emp
  3  order by deptno;

    DEPTNO ENAME
---------- ----------
        10 CLARK
        10 KING
        10 MILLER
        20 JONES
        20 FORD
        20 ADAMS
        20 SMITH
        20 SCOTT
        30 WARD
        30 TURNER
        30 ALLEN
        30 JAMES
        30 BLAKE
        30 MARTIN

14 rows selected.

SQL> sho user
USER is "SCOTT"
SQL> CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR )
  2    RETURN  VARCHAR2 IS
  3      ret VARCHAR2(32000);
  4      tmp VARCHAR2(4000);
  5  BEGIN
  6      loop
  7          fetch cur into tmp;
  8          exit when cur%NOTFOUND;
  9              ret := ret || '  ' || tmp;
 10      end loop;
 11      RETURN ret;
 12  END;
 13  /

Function created.

SQL>  SELECT distinct
  2       deptno||' '||
  3       SUBSTR(CONCAT_LIST(CURSOR(
  4           SELECT ename FROM emp ee WHERE e.deptno = ee.deptno
  5       )),1) employees
  6   FROM emp e
  7  /

EMPLOYEES
----------------------------------------------------------------------------------
20   SMITH  JONES  SCOTT  ADAMS  FORD
30   ALLEN  WARD  MARTIN  BLAKE  TURNER  JAMES
10   CLARK  KING  MILLER

SQL> 


Many more at
Not only for 10g


sriram Smile

[Updated on: Wed, 09 December 2009 05:12]

Report message to a moderator

Re: Sql [message #434460 is a reply to message #434440] Wed, 09 December 2009 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What about "How would you do it using SQL"?
And "Can not this one have alternative??? " (meaning alternative to Kevin article)
Please once again read the whole topic and do not post your useless answer just to take pleasure to show you can do it.

Regards
Michel
Re: Sql [message #434490 is a reply to message #434460] Wed, 09 December 2009 06:36 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
>What about "How would you do it using SQL"?

At first he asked for using sql (reply to delna`s answer).
Later he said that wm_concat is not documented (even you suggested).
If it is documented then ? So he is ready to use for the user defined functions ( My openion).

> "Can not this one have alternative??? " (meaning alternative to Kevin article)

I am sure the article provided by me is different from kevin`s(http://www.orafaq.com/node/2290).

> do not post your useless answer just to take pleasure to show you can do it.

I never said that me only can do it...or some thing else...

And all my answers are not for me and not for others for just to say That I can do this

As he said he found kevin`s....i posted another link from oracle base.

Thanks for your comment like Useless Mr.Michel.

And one more right know i have posted 384 message...Can you count how many of them are useless so that i can stop posting completely...in this forum forever

sriram

[Updated on: Wed, 09 December 2009 06:45]

Report message to a moderator

Re: Sql [message #434495 is a reply to message #434490] Wed, 09 December 2009 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Sure there are many difference between "your" CONCAT_LIST function and "kev_get_string_list" Kevin's one: you open the cursor before calling the function when Kevin open it inside it.

Regards
Michel
Re: Sql [message #434497 is a reply to message #434495] Wed, 09 December 2009 06:50 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
I am not talking about my concat_list function...I am talking about the URL posted....even it has the same what ever kevin has in his post but inaddition.

And i clearly said that is from oracle base.

sriram
Re: Sql [message #434498 is a reply to message #434497] Wed, 09 December 2009 06:54 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
And OP asked for ALTERNATIVES and only that.

Regards
Michel
Previous Topic: Assistant Required
Next Topic: Oracle Architecture
Goto Forum:
  


Current Time: Fri Feb 14 16:08:31 CST 2025