Flatten multiple values into 1 line 1

[PeterConnick]

Oracle 9i

I have a query like

SELECT a.providerid, a.languageid
FROM tblProviderLanguages a

10 1
10 2
10 3
20 2
20 3
20 5

I will join a.languageid later to get the language code


Any idea how I can retrieve the data like this:

10 1 2 3
20 2 3 5

Please note the languageid can have 1000 different values

 

[SantaMufasa]

Possible solutions for you depend upon:

- Will there always be a consistent number of values (e.g., 3 or less, or, 8 or less, et cetera) for each distinct providerid?
- Is there significance to the output column into which the "originally stacked" values fall?
- Do you want the various values in columns or do you want them comma separated?
- If you want them comma separated, do you (or your shop) have an aversion to, or restriction against, solving this problem with a PL/SQL user-defined function? ...that you might invoke as:

SELECT distinct a.providerid, get_lang(a.providerid)
  FROM tblProviderLanguages a;

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[PeterConnick]

Mufasa,

- Will there always be a consistent number of values (e.g., 3 or less, or, 8 or less, et cetera) for each distinct providerid?

There will always be at least one 1 value per distinct providerid, but there is no limit to the number of languages the providerid could have.


- Is there significance to the output column into which the "originally stacked" values fall?

Yes

I actually want 7 values

Language1
Language2
Language3
Language4
Language5
Language6
Language7

- Do you want the various values in columns or do you want them comma separated?

Ideally, I would like to get 2 columns

Providerid Languageid

10 1,2,3,4,5,6,7
11 1,2, , , , ,

- If you want them comma separated, do you (or your shop) have an aversion to, or restriction against, solving this problem with a PL/SQL user-defined function? ...that you might invoke as:

I am new to PL/SQL but a user- defined function would be perfect.

I am not sure how to create the get_lang function

Thanks,

Peter

 

[SantaMufasa]

Actually, Peter, your preference is the simplest (in my opinion) of all options:

select * from tblproviderlanguages;

PROVIDERID LANGUAGEID
---------- ----------
        10          1
        10          2
        10          3
        20          2
        20          3
        20          5

create or replace function get_lang (prov_in number) return varchar2 is
    hold_str varchar2(2000);
begin
    for x in (select languageid from tblproviderlanguages
               where providerid = prov_in) loop
        hold_str := hold_str||','||x.languageid;
    end loop;
    return ltrim(hold_str,',');
end;
/

Function created.

col b heading "Languages" format a20
SELECT distinct a.providerid, get_lang(a.providerid) b
  FROM tblProviderLanguages a;

PROVIDERID Languages
---------- ---------
        10 1,2,3
        20 2,3,5

2 rows selected.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[PeterConnick]

Mufasa,

Let start off by saying, thank you. I have been spinning my wheels all day trying to figure this out.

I created the function and ran the SQL and it worked perfectly.

Actually, what I am trying to do is capture the first 7 languages (omit 8 and above).

If the person only speaks 1 language then the second language would be null or ''.

Example

select * from tblproviderlanguages;

PROVIDERID LANGUAGEID
---------- ----------
10 1
10 2
10 3
10 4
10 5
10 6
10 7
10 8
11 1
11 2

Providerid Languageid

10 1,2,3,4,5,6,7
11 1,2, , , , ,


I don’t need to do yet, but I am sure I will have to do this down the road. How would I put the value (different languages) into columns?

Thanks again I truly appreciate it.

Peter

 

[SantaMufasa]

Peter,

Do you want 6 commas to appear regardless of how many, or how few, languages appear for each provider (to a maximum of 7)?

>>> How would I put the value (different languages) into columns?

Do you mean, "How can one produce these results?":

10      1      2      3      4      5      6      7
11      1      2

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[PeterConnick]

Mufasa,

Do you want 6 commas to appear regardless of how many, or how few, languages appear for each provider (to a maximum of 7)?

Yes, I want 6 commas to appear for each provider and 7 is the maximum number even if there are 8 or more languages.

What I am actually doing is creating a text file that will be imported into another system.

In the file definitions there are 7 language fields. I need to place a value in each field separated by a comma.

Example

1,,,,,, or 1,2,,,,, or 1,2,3,,,, or 1,2,3,4,,, or 1,2,3,4,5,, or 1,2,3,4,5,6, or 1,2,3,4,5,6,7


>>> How would I put the value (different languages) into columns?

Do you mean, "How can one produce these results?":

Yes

The table would have the follow columns

ProviderID,
Language1,
Language2,
Language3,
Language4,
Language5,
Language6,
Language7

If the provider only had one language, I would populate the remaining 6 languages fields will a null value.

I would then load these values into a table. I would than be able to report off this table in some of my other reports.

Thanks again for your help,

Peter