Oracle, trying to convert rows into a single column
Hi I am trying to convert multiple rows into one column: I am trying to use the listagg but the editor does not like the WITHIN GROUP. Basically this query returns the member_id and material sent, each material sent will be a seperate row. I would like to see on row with the member_id and list each material sent in one column.
SELECT md.member_id ,
listagg (t.hd_material_type_desc, ',') WITHIN GROUP
(ORDER BY t.hd_material_type_desc) material
FROM hdkr.activity_summary act ,
hdkr.hd_material t ,
hdkr.member_dim md
WHERE act.person_id = md.person_id
AND act.client_id_dwh = 'ABC'
AND md.client_id_dwh = 'ABC'
AND md.record_status_code = 'ACTIVE'
AND t.record_status_code = 'ACTIVE'
AND act.hd_material_key = t.hd_material_key
AND act.activity_id = 11
AND act.activity_status_id = 303
AND act.activity_result_id IN (203, 218, 219)
AND t.hd_material_id IN (4000, 4018, 4019, 4052, 4062, 4099, 4100, 4101, 4102, 4103, 4114, 4119, 4124, 4136, 4138, 4140, 4144, 4148, 4149, 4157, 4160, 4161, 4162, 4175, 4176, 4177, 4178, 4197, 4200, 4202, 4203, 4204, 4205, 4208, 4209, 4210, 4211, 4212, 4213, 4214, 4215, 4216, 4217, 4218, 4219, 4220, 4221, 4222, 4223, 4224, 4225, 4226, 4227, 4228, 4229, 4230, 4231, 4232, 4233, 4234)
AND TO_CHAR(act.last_upd_date,
SELECT md.member_id ,
listagg (t.hd_material_type_desc, ',') WITHIN GROUP
(ORDER BY t.hd_material_type_desc) material
FROM hdkr.activity_summary act ,
hdkr.hd_material t ,
hdkr.member_dim md
WHERE act.person_id = md.person_id
AND act.client_id_dwh = 'ABC'
AND md.client_id_dwh = 'ABC'
AND md.record_status_code = 'ACTIVE'
AND t.record_status_code = 'ACTIVE'
AND act.hd_material_key = t.hd_material_key
AND act.activity_id = 11
AND act.activity_status_id = 303
AND act.activity_result_id IN (203, 218, 219)
AND t.hd_material_id IN (4000, 4018, 4019, 4052, 4062, 4099, 4100, 4101, 4102, 4103, 4114, 4119, 4124, 4136, 4138, 4140, 4144, 4148, 4149, 4157, 4160, 4161, 4162, 4175, 4176, 4177, 4178, 4197, 4200, 4202, 4203, 4204, 4205, 4208, 4209, 4210, 4211, 4212, 4213, 4214, 4215, 4216, 4217, 4218, 4219, 4220, 4221, 4222, 4223, 4224, 4225, 4226, 4227, 4228, 4229, 4230, 4231, 4232, 4233, 4234)
AND TO_CHAR(act.last_upd_date,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
and I agree with the dte recommendation
change
TO_CHAR(act.last_upd_date,
to
act.last_up_date >= to_date('20110401','yyyymm
and act.last_up_date < to_date('20110430','yyyymm
change
TO_CHAR(act.last_upd_date,
to
act.last_up_date >= to_date('20110401','yyyymm
and act.last_up_date < to_date('20110430','yyyymm
ASKER
The SQL worked for me, but now I have another wrench, I tried to add my Concatenation to the EXTRACt and it doesn't like it. Can you cancat with this option?
EXTRACT( XMLAGG(XMLELEMENT("s", t.hd_material_type_desc
|| t.hd_material_title ',')
ORDER BY t.hd_material_type_desc), '/s/text()').getclobval() Health_Info_Sent
I don't know what you're trying to concatenate so it's hard to say what is correct or not.
but, in any case, you have left off a concatenation operator
t.hd_material_type_desc || t.hd_material_title || ',')
but, in any case, you have left off a concatenation operator
t.hd_material_type_desc || t.hd_material_title || ',')
that correction will produce a string like this...
desc1title1,desc2title2,de
is that what you want?
if not, please specify what functionality you are trying to achieve.
Also note, if you are adding/changing the functionality of the question originally asked, then this question should be closed and these changes should be a new question.
desc1title1,desc2title2,de
is that what you want?
if not, please specify what functionality you are trying to achieve.
Also note, if you are adding/changing the functionality of the question originally asked, then this question should be closed and these changes should be a new question.
ASKER
I need to concat the Type desc and the material title.
like I showed? or something else?
ASKER
like you showed sorry, multitasking :-(
then correct as shown with the missing || operator
ASKER
Thank you very much, I have been out of Oracle SQL for a couple of years and working on getting back up to speed. Thank you!
Also:
AND TO_CHAR(act.last_upd_date,
I would convert your other strings to dates and leave act.last_upd_date as a date.