Solved

concatenate

Posted on 2011-02-22
1
433 Views
Last Modified: 2012-05-11
Hi I am using Oracle 9i with following query
/* Formatted on 22/02/2011 10:19:33 AM (QP5 v5.136.908.31019) */
SELECT ELECTMEMBER.MEMBERNO,
       TITLE.DESCRIPTION || ' ' || ELECTMEMBER.NAME AS NAME,
       TRIM(SUBSTR (ELECTMEMBERADDRESS.ADDRESS,
                    1,
                    INSTR (ELECTMEMBERADDRESS.ADDRESS,
                           '$',
                           1,
                           1)
                    - 1))
          AS ADDRESS1,
       TRIM(SUBSTR (ELECTMEMBERADDRESS.ADDRESS,
                    INSTR (ELECTMEMBERADDRESS.ADDRESS,
                           '$',
                           1,
                           1)
                    + 1,
                    (INSTR (ELECTMEMBERADDRESS.ADDRESS,
                            '$',
                            1,
                            2)
                     - 1)
                    - (INSTR (ELECTMEMBERADDRESS.ADDRESS,
                              '$',
                              1,
                              1))))
          AS ADDRESS2,
       TRIM(SUBSTR (ELECTMEMBERADDRESS.ADDRESS,
                    INSTR (ELECTMEMBERADDRESS.ADDRESS,
                           '$',
                           1,
                           2)
                    + 1,
                    (INSTR (ELECTMEMBERADDRESS.ADDRESS,
                            '$',
                            1,
                            3)
                     - 1)
                    - (INSTR (ELECTMEMBERADDRESS.ADDRESS,
                              '$',
                              1,
                              2))))
          AS ADDRESS3,
       TRIM(SUBSTR (ELECTMEMBERADDRESS.ADDRESS,
                    INSTR (ELECTMEMBERADDRESS.ADDRESS,
                           '$',
                           1,
                           3)
                    + 1))
          AS ADDRESS4,
          ELECTMEMBERADDRESS.CITYOTHER
       || CITY.DESCRIPTION
       || ' - '
       || ELECTMEMBERADDRESS.PINCODE
          AS CITY,
       TRUNC (TO_DATE (ELECTMEMBER.ELECTDATE, 'DD/MM/YYYY')) AS ELECTDATE,
       TRUNC (TO_DATE (ELECTMEMBER.EFFECTIVEDATEFROM, 'DD/MM/YYYY'))
          AS EFFECTIVEFROM,
       TRUNC (TO_DATE (ELECTMEMBER.EFFECTIVEDATETO, 'DD/MM/YYYY'))
          AS EFFECTIVETO,
       CATEGORYSUBGROUP.DESCRIPTION AS CATEGORYSUBGROUP,
       SUBSCRIPTION.DESCRIPTION AS SUBSCRIPTION
  FROM ELECTMEMBER
       LEFT OUTER JOIN ELECTMEMBERADDRESS
          ON ELECTMEMBER.ELECTMEMBER_ID = ELECTMEMBERADDRESS.ELECTMEMBER_ID
       LEFT OUTER JOIN CATEGORYSUBGROUP
          ON CATEGORYSUBGROUP.CATEGORYSUBGROUP_ID =
                ELECTMEMBER.CATEGORYSUBGROUP_ID
       LEFT OUTER JOIN CITY
          ON CITY.CITY_ID = ELECTMEMBERADDRESS.CITY_ID
       LEFT OUTER JOIN COUNTRY
          ON COUNTRY.COUNTRY_ID = ELECTMEMBERADDRESS.COUNTRY_ID
       LEFT OUTER JOIN STATE
          ON STATE.STATE_ID = ELECTMEMBERADDRESS.STATE_ID
       LEFT OUTER JOIN TITLE
          ON TITLE.TITLE_ID = ELECTMEMBER.TITLE_ID
       LEFT OUTER JOIN ELECTSUBSCRIPTIONUSER
          ON ELECTMEMBER.ELECTMEMBER_ID =
                ELECTSUBSCRIPTIONUSER.ELECTMEMBER_ID
       LEFT OUTER JOIN SUBSCRIPTION
          ON ELECTSUBSCRIPTIONUSER.SUBSCRIPTION_ID =
                SUBSCRIPTION.SUBSCRIPTION_ID
       LEFT OUTER JOIN ELECTMEMBERTYPE
          ON ELECTMEMBER.ELECTMEMBERTYPE_ID =
                ELECTMEMBERTYPE.ELECTMEMBERTYPE_ID
 WHERE ELECTMEMBER.ELECTDATE BETWEEN NVL (
                                        TO_DATE ('10/02/2009', 'DD/MM/YYYY'),
                                        ELECTMEMBER.ELECTDATE)
                                 AND  NVL (
                                         TO_DATE ('10/02/2012', 'DD/MM/YYYY'),
                                         ELECTMEMBER.ELECTDATE)
       AND ELECTMEMBER.ISACTIVE = 1
       AND ELECTMEMBERADDRESS.ISCORRESPONDENCEADDRESS=1
       AND ELECTMEMBERTYPE.ELECTMEMBERTYPE.ISELECT = 1
       AND ELECTMEMBER.MEMBERNO='K-293'
       
       I want to concatenate  SUBSCRIPTION.DESCRIPTION AS SUBSCRIPTION as per excel sheet shown in desired output. Problem is I do not know how many rows will come so I cant hard code it
elect.xls
0
Comment
Question by:GRChandrashekar
1 Comment
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 34958776
This link contains all string aggregation techniques. You can pick the one which suits you. just note you need to pick the one which works in 9i

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php
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.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 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

22 Experts available now in Live!

Get 1:1 Help Now