Link

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Fri, 01 Feb 2008 9:24 am

E ai Galera!

to have a problem, I don't know if you can do that in a query but the problem is this ... I'm putting together a query to get the data to a doctor, the table is PROVIDER ... then I select query name, age, address, etc, etc and specialty. However, 1 physician may have no specialties ... to make this relationship and get a description of the specialty has the table where ESP_MED has the code of the service provider and specialty code ... and in the specialty where has the description.

Running the query with these relationships, the amount of records that will return will be equal to the amount of expertise .... However I need that returns only 1 record per doctor.

Do something concatenate to appear all specialties on the same line separated by commas for example??

Thanks A Lot!!
renan_pre
Location: São Paulo - SP

M. Renan

Poston Fri, 01 Feb 2008 10:28 am

Friend, when I needed it, I did through PL-SQL, played the values to a variable (using cursor), and then selected in the query.
cron_rj
Location: Rio de Janeiro RJ

Poston Fri, 01 Feb 2008 10:32 am

Then brother ...

I'm not developing in forms and reports. Is a tool for developing reports that does not allow pl/sql ... She works with views ...

Is there another way to do??

Hug
renan_pre
Location: São Paulo - SP

M. Renan

Poston Fri, 01 Feb 2008 10:45 am

And ai Renan beauty??

to make yea, look here on the site or on the net by the word \"PIVOT TABLE\". this [url = : http//www . com/node/. orafaq 1871] link [/url] has an article talking about this ...

[]'s!!
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Fri, 01 Feb 2008 10:56 am

Thanks Christian!

Not tested yet but for example that has the link you sent will solve my problem!

Hug!
renan_pre
Location: São Paulo - SP

M. Renan

Poston Fri, 01 Feb 2008 1:24 pm

I found a way to do too, here it worked. Follow the link: http://www.oracle.com/technology/oramag ... 01606.html
cron_rj
Location: Rio de Janeiro RJ

Poston Thu, 07 Feb 2008 9:12 am

Show de bola.
I will put here the text so as not to lose this hint:
Code: 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


dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Tue, 12 Feb 2008 4:23 pm

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

Code: Select all
row_number() OVER (Partition by ID order by &Column_Sort_Name )
more precisely, what is this row_number (), what is this OVER and what the hell faiz this Partition = D if anyone can help me = D thanks ...
gokden
Location: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas



Return to SQL

Who is online

Users browsing this forum: No registered users and 3 guests