[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle, trying to convert rows into a single column

Posted on 2011-05-11
11
Medium Priority
?
638 Views
Last Modified: 2012-05-11
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, 'yyyymmdd') BETWEEN '20110401' AND '20110430'
0
Comment
Question by:knamc
  • 6
  • 4
11 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35740603
What 'editor'?  Try running it from sqlplus.

Also:
AND TO_CHAR(act.last_upd_date, 'yyyymmdd') BETWEEN '20110401' AND '20110430'


I would convert your other strings to dates and leave act.last_upd_date as a date.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 35740617
if you are not using 11gR2 then listagg isn't supported

try this in 10g and above
SELECT   md.member_id,
         EXTRACT(
             XMLAGG(XMLELEMENT("s", t.hd_material_type_desc || ',') ORDER BY t.hd_material_type_desc),
             '/s/text()').getclobval()
             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, 'yyyymmdd') BETWEEN '20110401' AND '20110430'
GROUP BY md.member_id

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35740641
and I agree with the dte recommendation

change

TO_CHAR(act.last_upd_date, 'yyyymmdd') BETWEEN '20110401'  AND '20110430'


to  

act.last_up_date >= to_date('20110401','yyyymmdd')
and act.last_up_date < to_date('20110430','yyyymmdd') + 1
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:knamc
ID: 35741183

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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35741228
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 || ',')



0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35741242
that correction will produce a string like this...


desc1title1,desc2title2,desc3title3

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.
0
 

Author Comment

by:knamc
ID: 35741279
I need to concat the Type desc and the material title.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35741331
like I showed? or something else?
0
 

Author Comment

by:knamc
ID: 35741389
like you showed sorry,  multitasking :-(
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35741411
then correct as shown with the missing || operator
0
 

Author Closing Comment

by:knamc
ID: 35741991
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!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question