Concatenation

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
renan_pre
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 50
Joined: Tue, 10 Apr 2007 11:08 am
Location: São Paulo - SP
M. Renan

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 !!
cron_rj
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 25
Joined: Wed, 19 Dec 2007 9:10 am
Location: Rio de Janeiro RJ

Friend,

When I needed this, I did through PL-SQL, I played the values ??for a variable (using cursor), and then I selected it in the query.
renan_pre
Rank: Programador Sênior
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
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
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 !!
renan_pre
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 50
Joined: Tue, 10 Apr 2007 11:08 am
Location: São Paulo - SP
M. Renan

Thanks Cristiano!

I have not tested yet but for the example you have in the link you sent will solve my problem!

Hugs!
cron_rj
Rank: Programador Júnior
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
User avatar
dr_gori
Moderador
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

Cool.
I'll put the text here so we do not miss this tip:

Select all

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 
 
 
gokden
Rank: DBA Pleno
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

Personal, sorry for my ignorance, but could someone explain to me what makes this line?

Select all

row_number() OVER (Partition by ID order by &Column_Sort_Name )
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 ...
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 4 guests