Solved

Oracle SQL:  Concatenation of query

Posted on 2011-03-16
6
689 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
  • 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 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

929 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now