Sql [message #433882] |
Fri, 04 December 2009 16:48  |
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 #433917 is a reply to message #433910] |
Sat, 05 December 2009 00:56   |
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   |
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   |
 |
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 #433922 is a reply to message #433920] |
Sat, 05 December 2009 01:08   |
Its_me_ved
Messages: 979 Registered: October 2009 Location: India
|
Senior Member |
|
|
Michel Cadot wrote on Sat, 05 December 2009 01:01Its_me_ved wrote on Sat, 05 December 2009 08:00Sir,
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
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 #433924 is a reply to message #433922] |
Sat, 05 December 2009 01:14   |
 |
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   |
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! 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 #433927 is a reply to message #433926] |
Sat, 05 December 2009 01:59   |
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   |
 |
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   |
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 
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   |
 |
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:32Quote:
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   |
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   |
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   |
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 #433961 is a reply to message #433882] |
Sat, 05 December 2009 14:15   |
 |
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   |
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 #434440 is a reply to message #433917] |
Wed, 09 December 2009 05:10   |
 |
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
[Updated on: Wed, 09 December 2009 05:12] Report message to a moderator
|
|
|
|
Re: Sql [message #434490 is a reply to message #434460] |
Wed, 09 December 2009 06:36   |
 |
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 #434498 is a reply to message #434497] |
Wed, 09 December 2009 06:54  |
 |
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
|
|
|