Link to home
Start Free TrialLog in
Avatar of Sara_j_11
Sara_j_11

asked on

sql

I have a sql that returns the favourite colours of cutomer. the result has about 5 columns out of which 3 columns are identical,
but 2 column values such as colour and another column may have 2 or more values for one cutomer.
for example:

12  James  ...... red
12  james  ...... blue
12 James ....... yellow


So the result prints 3 records for the same customer. I want only one row in this case.
12  james ....... red yellow blue

How do I do this?

Please send code
Avatar of arioh
arioh
Flag of Russian Federation image

for a table like this:
create table MYUSERS
(
  ID    NUMBER,
  NAME  VARCHAR2(400),
  COLOR VARCHAR2(400)
)

you can use this query:
    SELECT
      ID,
      NAME,
      LTRIM( MAX( SYS_CONNECT_BY_PATH( COLOR, ' ' ) ) KEEP( DENSE_RANK LAST ORDER BY curr ) ) as COLORS
    FROM
      ( SELECT
          ID,
          NAME,
          COLOR,
          ROW_NUMBER() OVER (partition by ID, NAME order by COLOR) AS curr,
          ROW_NUMBER() OVER (partition by ID, NAME order by COLOR) -1 AS prev
        FROM
          MYUSERS
      )
    GROUP BY
      ID,
      NAME
    CONNECT BY
      prev = PRIOR curr
    START WITH
      curr = 1

it will work on Oracle9i and later
ASKER CERTIFIED SOLUTION
Avatar of Sujith
Sujith
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sara_j_11
Sara_j_11

ASKER

I not only have the column called colour , that is repeating, but also another column favourite dessert. How do i add that second column into this code?
select id, ename, trim(' ' from sys_connect_by_path(color, ' ') )
from (
select id, ename, color,
row_number() over(partition by id order by color) rn,
count(*) over(partition by id) cp
from tbl1
)
where rn = cp
start with rn = 1
connect by prior id = id
and prior rn = rn - 1
/
for example

12  James  ...... red        icecream
12  james  ...... blue       pops
12 James ....... yellow     peach cobbler
12  James .......blue        apple cobbler
What is the way you are expecting the results for this example?
there should not be any dups for a patient. I mean it should be just one row

12   James   ........   red blue yellow  icecream pops  peach cobbler aplle cobbler
hi sara_j_11,

look at this link http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php and it has the answers for what you are looking for.

Thanks
You can still use the function concept, just create another for the desserts. Note the use of the initcap function since you have both James and james in the name column field.

SQL> select * from yourtable;

        ID NAME  COLOR    DESSERT
---------- ----- -------- --------------
        12 James red      icecream
        12 james blue     pops
        12 James yellow   peach cobbler
        12 James blue     apple cobbler

SQL> select id, initcap(name) name, get_colors(id) colors, get_desserts(id) desserts
  2  from yourtable
  3  group by id, initcap(name);

        ID NAME  COLORS               DESSERTS
---------- ----- -------------------- ------------------------------------------
        12 James blue,red,yellow      icecream,pops,peach cobbler,apple cobbler