Solved

Oracle SQL:  Concatenation of query

Posted on 2011-03-16
6
703 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 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
A new era in Cloud training has arrived.

A day that will go down in Cloud history.. But are you ready for it? Will you accept this Cloud challenge?

 

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

627 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