What's up guys!
To with a problem, I do not know if you can do this in a query but the problem is the following ... I'm riding a query to pick up the data from a doctor, the table is provider. .. then na query I select name, age, address, etc, etc. and specialty. However, 1 doctor can have n specialties ... To make this relationship and get the description of the specialty has the SP_MED table where you have the provider code and code of the specialty ... and in the table specialty where you have the description.
running query with these relationships, the number of records that will return will be equal to the quantity of specialties .... but I need to return only 1 record by doctor ...
can do something of the type concatenar to appear all the specialties on the same comma separated by comma for example ???
Thanks !!
Concatenation
-
- Rank: Programador Sênior
- Posts: 50
- Joined: Tue, 10 Apr 2007 11:08 am
- Location: São Paulo - SP
M. Renan
Then Brother ...
I'm not developing in forms ... and neither reports ... It's a tool for developing reports that does not allow PL / SQL ... She works With views ...
Do you have another way to do ??
Embrace
I'm not developing in forms ... and neither reports ... It's a tool for developing reports that does not allow PL / SQL ... She works With views ...
Do you have another way to do ??
Embrace
-
- Rank: DBA Sênior
- Posts: 365
- Joined: Tue, 24 May 2005 2:24 pm
- Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP
Araraquara - SP
And ai renan beauty ??
to do yes, look here on the site or net by the word "pivot table".
In this [url=http://www.orafaq.com/node/1871]link it has an article Speaking about this ..
[] 's !!
to do yes, look here on the site or net by the word "pivot table".
In this [url=http://www.orafaq.com/node/1871]link it has an article Speaking about this ..
[] 's !!
-
- Rank: Programador Júnior
- Posts: 25
- Joined: Wed, 19 Dec 2007 9:10 am
- Location: Rio de Janeiro RJ
I found a way to do too, it worked here. Follows the link:
http://www.oracle.com/technology/oramag ... 01606.html
http://www.oracle.com/technology/oramag ... 01606.html
- dr_gori
- Moderador
- Posts: 5024
- Joined: Mon, 03 May 2004 3:08 pm
- Location: Portland, OR USA
- Contact:
Thomas F. G
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Cool.
I'll put the text here so we do not miss this tip:
I'll put the text here so we do not miss this tip:
Tip of the Week
Tip for Week of October 16, 2006
Concatenate Multiple Column Records
This tip comes from Galina Petrenko, a Sr. Programmer Analyst at Harte-Hanks Inc. in Langhorne, PA.
In Oracle9i Database and later, it's possible to rotate the vertical presentation of data into horizontal presentation. The following demonstrates a simple way (without XML parsers or PL/SQL stored structures) to convert vertical presentation into horizontal presentation; it will concatenate multiple column records into a single string.
-- Temporary Data
create table TMP_TEST ( ID NUMBER, NumVal NUMBER, STRVAL VARCHAR2(32) );
insert into TMP_TEST values(1, 100, 'Hello');
insert into TMP_TEST values(1,-100, 'World');
insert into TMP_TEST values(2, 1, 'Concatenate');
insert into TMP_TEST values(2, 2, 'In String');
insert into TMP_TEST values(2, 3, 'using Connect By');
insert into TMP_TEST values(2, 4, 'Using SYS_CONNECT_BY_PATH');
commit;
-- Check Vertical Data Presentation
select * from TMP_TEST;
ID NUMVAL STRVAL
---------- ---------- --------------------------------
1 100 Hello
1 -100 World
2 1 Concatenate
2 2 In String
2 3 using Connect By
2 4 Using SYS_CONNECT_BY_PATH
6 rows selected.
-- Get Concatenated String with Specific Delimiter
def Delimiter=" "
SELECT
ID,
SUBSTR(MAX(REPLACE(
SYS_CONNECT_BY_PATH(STRVAL, '/')
,'/','&Delimiter')),2) Concatenated_String
FROM (
select A.*,
row_number() OVER (Partition by ID order by ID) ROW#
from TMP_TEST A)
START WITH ROW#=1
CONNECT BY PRIOR ID=ID AND PRIOR row# = row# -1
GROUP BY ID
;
ID CONCATENATED_STRING
---------- -----------------------------------------------------------------
1 Hello World
2 Concatenate In String using Connect By Using SYS_CONNECT_BY_PATH
-- More Generic:
def Column_Sort_Name=ID
def Column_Name=NumVal
def Delimiter="|"
SELECT
ID,
SUBSTR(MAX(REPLACE(
SYS_CONNECT_BY_PATH( &Column_Name , '/')
,'/','&Delimiter')),2) Concatenated_String
FROM (
select
A.*,
row_number() OVER (Partition by ID order by &Column_Sort_Name ) ROW#
from TMP_TEST A)
START WITH ROW#=1
CONNECT BY PRIOR &Column_Sort_Name = &Column_Sort_Name
AND PRIOR row# = row# -1
GROUP BY &Column_Sort_Name
;
ID CONCATENATED_STRING
---------- -----------------------------------------------------------------
1 100|-100
2 1|2|3|4
def Delimiter=","
ID CONCATENATED_STRING
---------- -----------------------------------------------------------------
1 100,-100
2 1,2,3,4
-
- Rank: DBA Pleno
- Posts: 264
- Joined: Sun, 19 Aug 2007 8:18 pm
- Location: Ribeirão Preto - SP
Lucas de Souza
OCA Developer
Analista de sistemas
OCA Developer
Analista de sistemas
Personal, sorry for my ignorance, but could someone explain to me what makes this line?
more precisely, what is this row_number (), which is this over and what the hell does this partition = D
If someone can help me = D
grateful ...
row_number() OVER (Partition by ID order by &Column_Sort_Name )
If someone can help me = D
grateful ...
-
- Information
-
Who is online
Users browsing this forum: No registered users and 17 guests