Can this be done with sql? 3

[bookouri]

I suspect that this cant be done with a select but if anybody could point me to any way it can be done, it would be appreciated.

I have a table with codes associated with customers. The customers can have any number of 23 different codes or NO code.
The number of codes is subject to change so i dont want to create a vew with customer_id, col1, col2....col23 or anything like that if i can avoid it.

cutsomer_codes table has

customer_id code
1001 AB
1002 BB
1003 4B
1004 TT
1001 AA
1001 BB
1001 AB
1003 AB
1004 BC
1004 CC

What would be ideal would be a view that would give me a row per customer that would list all the codes that customer has like this.

customer_codes_view

customer_id col1 col2 col3 col4
1001 AB AA BB AB
1002 BB
1003 4B AB
1004 TT BC CC

any suggestions would be appreciated

 

[SantaMufasa]

Bookouri,

I used your data, plus I added in a test row for Customer_ID "1000" that has no code:

select * from customer_codes;

CUSTOMER_ID CODE
----------- ----
       1001 AB
       1002 BB
       1003 4B
       1004 TT
       1001 AA
       1001 BB
       1001 AB
       1003 AB
       1004 BC
       1004 CC
       1000

And since you can have 23 different codes, I suggest that rather than producing a specific number of column headings for each possible column, you simply string the codes together in a single display expression with the following code:

col codes format a15
 select customer_id,substr(max(sys_connect_by_path(code,',')),2) codes
   from (select customer_id,code,row_number() over (partition by customer_id order by code) rn
           from customer_codes)
  start with rn=1
connect by prior rn = rn - 1
    and prior customer_id = customer_id
  group by customer_id
  order by customer_id
/

CUSTOMER_ID CODES
----------- ---------------
       1000
       1001 AA,AB,AB,BB
       1002 BB
       1003 4B,AB
       1004 BC,CC,TT

Let us know if this satisfies your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[bookouri]

That is perfect! and I dont even understand lots of whats going on there. There's several things in there I have never seen used before. It would be great if you had time to do a brief walk through of whats going on. I've never seen anything like the "sys_connect_by_path" or the "over partition by" or the "connect by prior" for example. Im going to look them up though.

thanks again

 

[SantaMufasa]

Bookouri,

First of all, credit must go to our Tek-Tips Oracle MVP colleague, taupirho, who was the first to post this code construct for producing a "pivot" result set here on Tek-Tips.

If you have not received training in the functionality of the code modules, above, then you have every right to be puzzled...this is one of the most conceptually complex code examples in the Oracle World...there are several separate code concepts working together in the above code:[UL][li]"start with...connect by...": Oracle's extremely powerful hierarchical-tree-walking code (Google: "start with connect by" 28M+ hits)[/li][li]"...sys_connect_by_path...": a powerful extension to Oracle's tree-walking functionality (Google: "sys_connect_by_path" 9.9K+ hits)[/li][li]"...row_number() over (partition by...": one of several of Oracle's powerful Analytics functions. (Google: "Oracle analytics" 4.3M+ hits; "Oracle analytics examples" ~5.6M hits)[/li][li]"Select...from (select...": Oracle in-line views (Google: "oracle inline views" 796K+ hits)[/li][/ul]When I taught the above concepts as an Oracle University instructor, the presentation time for each of the above concepts occupied between .5 - 1 hour of classroom time each, so they are not "quick-and-dirty" concepts that one can teach appropriately here...each concept requires good presentation material and an opportunity for the learner to practice use of each concept against a sample table, such as Oracle Education's EMP or S_EMP tables.


But, at least now, you can see (from the above example) a working implemenation of all of the concepts in concert.

Although it would be difficult to present training on the concepts here, it is appropriate to answer for you specific code-behavior questions once you have at least an introductory proficiency of the above concepts.

Let us know how your investigation proceeds.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[cranebill]

Bows before SantaMufasa

 

[bookouri]

thanks again, it seems like there's no bottom to this stuff. the more i think i know, the more i run into that i have never seen before... i think this bit here will keep me busy for a while