Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle SQL:  Concatenation of query

Posted on 2011-03-16
6
Medium Priority
?
705 Views
Last Modified: 2012-05-11
Experts,

I have a query that I need to concatenate a couple columns, issue is that I am not sure how to.

Let me explain what I am trying to do.

1. For every duplicated "MS.TICKET_ID" # needs to show up only once
2.  Then I must concatenate the "ML.DESCRIPTION" column values into one row
3.  Then I must concatenate the "MC.CUSTOMER_NAME" column values into one row

So basically, I am trying to push multiples rows of duplicate ID # into 1 row. Including the description and customer name as well.
 
SELECT
        UDF_CONVERT_UNIX_DATETIME(MS.SCHEDULED_START_DATE_TIME, 'US/Eastern') AS "Start/Date Time",
        UDF_CONVERT_UNIX_DATETIME(MS.SCHEDULED_END_DATE_TIME, 'US/Eastern') AS "End/Date Time",
        UDF_CONVERT_UNIX_DATETIME(MS.IMPACT_START_DATE_TIME, 'US/Eastern') AS "Impact Start Date/Time",
        UDF_CONVERT_UNIX_DATETIME(MS.IMPACT_END_DATE_TIME, 'US/Eastern') AS "Impact End Date/Time",
        MS.IMPACT_SCOPE AS "Scope",
        MS.TICKET_ID,
        MS.CHANGE_DESCRIPTION AS "Summary",
        MS.SERVICE_IMPACT_DESCRIPTION,
        ML.DESCRIPTION,
        MC.CUSTOMER_NAME
FROM ((ARADMIN.SD_JOIN_MAINTENANCE_TO_RELATED MS 
    INNER JOIN ARADMIN.HD_AFFECTED_LOCATIONS ML ON MS.GUI_ID=ML.GUI_ID)
    INNER JOIN ARADMIN.MCD_CT_CUSTOMER_INFORMATION MC ON ML.DESCRIPTION=MC.DIVISIONHUB)
WHERE UDF_CONVERT_UNIX_DATETIME(MS.SCHEDULED_START_DATE_TIME, 'US/Eastern') >= (TRUNC(SYSDATE) + INTERVAL '5' DAY)
        AND UDF_CONVERT_UNIX_DATETIME(MS.SCHEDULED_START_DATE_TIME, 'US/Eastern') < (TRUNC(SYSDATE) + INTERVAL '21' DAY)
        AND MS.SERVICE_AFFECTED = 'Cellular Backhaul'
        OR MS.TICKET_ID='MNT-215211'

Open in new window

Output:

TICKET_ID	Summary	SERVICE_IMPACT_DESCRIPTION	DESCRIPTION	CUSTOMER_NAME
MNT-215211	Hub 4, 5, 20, and 27 CAR Routers configuration standards correction	Customer should not experience any service impact.	TPA45	MEF Certification Testing - Test Site B - H45
MNT-215211	Hub 4, 5, 20, and 27 CAR Routers configuration standards correction	Customer should not experience any service impact.	TPA42	GLT Total Office
MNT-215211	Hub 4, 5, 20, and 27 CAR Routers configuration standards correction	Customer should not experience any service impact.	TPA43	Lifepath Hospice - W Main St - 00383-011
MNT-215211	Hub 4, 5, 20, and 27 CAR Routers configuration standards correction	Customer should not experience any service impact.	TPA85	PCBOCC - Public Works
MNT-215211	Hub 4, 5, 20, and 27 CAR Routers configuration standards correction	Customer should not experience any service impact.	TPA12	COSP - Child's Park Community Center 2
MNT-215211	Hub 4, 5, 20, and 27 CAR Routers configuration standards correction	Customer should not experience any service impact.	TPA13	Baycare - Susie Kalkin
MNT-215211	Hub 4, 5, 20, and 27 CAR Routers configuration standards correction	Customer should not experience any service impact.	TPA57	Comcast 10G Interconnect
MNT-215211	Hub 4, 5, 20, and 27 CAR Routers configuration standards correction	Customer should not experience any service impact.	TPA09	Pinellas Suncoast Transit Authority - 3180 Central Ave
MNT-215211	Hub 4, 5, 20, and 27 CAR Routers configuration standards correction	Customer should not experience any service impact.	TPA37	Quasius Investment Corporation dba GCA
MNT-218286	Moving TAMP27-CAR2 Links between interfaces to balance traffic from Te0/8/0/5 to Te0/8/0/7	no impact expected	TPA27	TBWC - 731 S Parsons Ave
MNT-215211	Hub 4, 5, 20, and 27 CAR Routers configuration standards correction	Customer should not experience any service impact.	TPA27	TBWC - 731 S Parsons Ave

Open in new window

0
Comment
Question by:Maliki Hassani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 7

Expert Comment

by:Piloute
ID: 35148657
Hi Lance,

Why not only format your output insteand of going into such "format destructive" actions ? There's a lot of features that allow you to produce clean reports without changing the data in your tables (that is finally what I think you want to do).

If you use SQL*Plus :
- to show each TICKET_ID only once, enter this before running your query : 'BREAK ON TICKET_ID'
- to add a space (n lines) between distinct TICKET_IDs, just add ' SKIP 1' tou your BREAK statement : BREAK ON TICKET_ID SKIP 1


Now, if you still want to do what you originally asked for, here's a little example that does exactly what you want (from Tom Kyte) :
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:2196162600402

Cheers,
P



0
 
LVL 7

Accepted Solution

by:
Piloute earned 2000 total points
ID: 35148744
... and some other ideas here, one of them using the one I pointed out in the previous comment :
http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

Cheers,
P
0
 

Author Comment

by:Maliki Hassani
ID: 35148837
Thanks,  how and wher would I add the BREAK ON TICKET_ID SKIP 1?  I have tried it and it doesn't seem to work
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

Author Comment

by:Maliki Hassani
ID: 35148842
I am using Oracle 10g
0
 
LVL 7

Expert Comment

by:Piloute
ID: 35154802
It is a formatting option for SQL*Plus. Have a look at the doc here :

http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12009.htm

P
0
 
LVL 7

Expert Comment

by:Piloute
ID: 35154823
... in other words it is a command to run in SQL*Plus before your query. Additionnaly, you must have an 'order by' statement using the key you want to 'break' up the response.
0

Featured Post

Google Certified Professional - Cloud Architect

This course (1 of 3) is designed to help students who are interested in Google Cloud Platform (GCP) to become familiar with the platform, navigate the console and learn its capabilities. It will also prepare students for the Google Cloud Architect certification exam.

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.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

721 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