?
Solved

Oracle SQL:  Concatenation of query

Posted on 2011-03-16
6
Medium Priority
?
723 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 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
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 what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

571 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