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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
/
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
/
ASKER
for example
12 James ...... red icecream
12 james ...... blue pops
12 James ....... yellow peach cobbler
12 James .......blue apple cobbler
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?
ASKER
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
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
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
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
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