Reverse Table 2

[pkohli8]

Hi,
I have this table that contains Names and in other column, I have list of groups that person belongs to in CSV format.
This might be possible that many person can belong to one group.
so, what i am looking for is the reverse of the table. I want to list individual groups in one column and in next column , i want to display all names of person in CSV format that belongs to one group.

For example...Existing table
Name Group
Peter dcs_na_ias_wintel,default,dts_dss_na_prod_wintel
Jhon default,ins chase technology
Mike dcs_na_ias_wintel


Looking for result....

Group Name
dcs_na_ias_wintel Peter,Mike
default Peter,Jhon
dts_dss_na_prod_wintel Peter
ins chase technology Jhon

Thanks In advance

 

[SantaMufasa]

Pkohli8,

We certainly can propose one or more solutions to your need, but we would be building a solution upon a "bad-form" data structure. In a relational environment, using (as you call it) a "CSV" storage scheme badly disobeys the rules of normalised, well-behaving data structures.

First, do you have control over this data structure to the extent that you can fix it? If not, then who has? Whoever has control over the database structures should cause the data to reside in this fashion:

Peter      dcs_na_ias_wintel
Peter      default
Peter      dts_dss_na_prod_wintel
Jhon       default
Jhon       ins chase technology
Mike       dcs_na_ias_wintel

So, before we propose a "half-baked" solution based upon the "poor" data design, please respond to whether we can fix your data first.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[pkohli8]

Hi Mufasa,
Thanks for the reponse, I do not have control over database structure. I am just a report designer and i have only READ ONLY access to database.

So, manager asked me if i can generate a report in the format explained in the previous post, that will be good.

But, yeah I know the way data is stored in the table is not normalized. manager, right now he doesn't want to make any change in the database.

Thanks

 

[SantaMufasa]

If your manager believes that one can pivot non-normalised, comma-separated values with READ-ONLY access, then s/he is a better SELECT-er than I am.

Yes, one can provide a solution that resolves your request, but to do so, we must have (at least) the ability to create some user-defined PL/SQL objects (e.g., functions/packages).

If you, or someone else, has, at least, the "CREATE PROCEDURE" privilege, then we can resolve your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[pkohli8]

Hi Mufasa,
Thanks for quick reply,

Is it possible to normalize the above table without creating any procedure or function, I am thinking if you can tell me,
Then i can give normalized result to my manager and tell this is the best i can get from existing table.

one more thing...how can i upload the code along with the post(if i want to do in future)

Thanks

 

[SantaMufasa]

I'm actually trying to prove my earlier post wrong by creating a read-only script that will do what you requested.

how can i upload the code along with the post

I'm not sure what you mean...what code/post do you want to 'upload'? Do you mean that you want to save what we do here to your local disk?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[pkohli8]

Thanks Musafa,
by CODE i meant how can format CODE,
I have that figured out by myself, I have to use Use TGML (Tecumseh Group Mark-up Language) to format,

Thanks In Advance for your help..looking forward to your reply

 

[SantaMufasa]

Pkohli8,

Is this output formatting acceptable?:

Group                     NAME
------------------------- -----
dcs_na_ias_wintel         Mike
                          Peter
default                   Jhon
                          Peter
dts_dss_na_prod_wintel    Peter
ins chase technology      Jhon

6 rows selected.

If so, then we have a solution...if not, then you (or someone) needs "CREATE PROCEDURE" permissions long enough to create a single user-defined function to obtain results in this format:

Group                     NAME
------------------------- -----
dcs_na_ias_wintel         Mike,Peter
default                   Jhon,Peter
dts_dss_na_prod_wintel    Peter
ins chase technology      Jhon

4 rows selected.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[pkohli88]

Hi Mufasa,
Sorry for the late reply...but there was some problem with My Account, I tried to log in but it was not let me login in to the TEK-TIPS. So I send couple of emails to Tek -TIps support teams but never get any reply from them , so finally now i have created new accout.
The codeoutput formatting explained in previous post is completely acceptable.
Could you please send me query?

Thanks
pkohli88

 

[SantaMufasa]

Here is the code that produced the first of the two code samples in my "18 Oct 06 16:43" reply:

set feedback off
set echo off
var x char(55)
col most new_value most noprint
exec :x := '@_ abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
set termout off
select max(nvl(length(translate(grp,:x,'@')),0)+1) most from pkohli;
set termout on
break on grp
col grp heading "Group" format a25
/
REM Begin: Produces normalised rows
select substr(grp
                   ,decode(rn,1,1
                               ,instr(grp,',',1,rn-1)+1
                          )
                   ,decode(rn,currmax,length(grp)
                                     ,decode(rn,1,instr(grp,',')-1
                                                 ,instr(grp,',',1,rn)-
                                                  instr(grp,',',1,rn-1)-1
                                            )
                          )
                   ) grp
      ,name -- ,rn,currmax
from
(select p1.*, rn, nvl(length(translate(grp,:x,'@')),0)+1 currmax
  from pkohli p1
      ,(select rownum rn from all_tab_columns n
         where rownum <= &most)
 where rn <= nvl(length(translate(grp,:x,'@')),0)+1
)
 order by grp, name
-- End: Produces normalised rows
/

Group                     NAME
------------------------- -----
dcs_na_ias_wintel         Mike
                          Peter
default                   Jhon
                          Peter
dts_dss_na_prod_wintel    Peter
ins chase technology      Jhon

Let us know if this works as well for you, pkohli88

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[ddiamond]

Mufasa,

That is some slick SQL. After spending a couple of hours trying to understand how it works, I have a couple of questions:

1. I gather that currmax is trying to determine how many commas are in the field grp by removing any characters that are in the variable x. If grp contains characters that are not in x, and are not a comma, will this cause currmax to be over stated?
2. If currmax is greater than the number of commas, will this cause blank rows in your final result?

- Dan

 

[SantaMufasa]

ddiamond,

Absolutely correct to both questions. If someone wants to extrapolate the above code for a situation that has additional valid special characters in their data, then the universe of valid special characters should also appear in the literal assignment to ":x".

"Just say 'No' to de-normalised data." -- Nancy Reagan

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

 

[ddiamond]

Mufasa,

Thanks for the example. I hope I will never need to use it, but it never hurts to expand one's "bag of tricks".

- Dan