• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

concatenate

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
GRChandrashekar
Asked:
GRChandrashekar
1 Solution
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now