Solved

Oracle SQL:  Concatenation of query

Posted on 2011-03-16
6
690 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

813 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

13 Experts available now in Live!

Get 1:1 Help Now