Concat values into single row
Posted: Thu Nov 27, 2008 12:46 am
Hi All
I have a scenario as below
input - oracle table
============
col1 col2
-------------
1 Sports
3 News
1 News
2 Weather
1 Weather
3 Sports
My output should have
===============
col1 col2
-------------------------
1 Sports News Weather
2 Weather
3 Sports News
Currently I'm doing it by a SQL query given below
-------------------------
SELECT /*+ INDEX(PP_TMP_CUST_SERVICE_RECORD PP_TMP_CUST_SERVICE_RECORD(MSISDN, PARTYID)) */
MSISDN,
PARTYID,
REPLACE(SUBSTR(MAX(SYS_CONNECT_BY_PATH(IMPLICIT_SEARCH_STRING, ':')), 2),':',' ') CONC_STRING,
date_key
FROM
(
SELECT
MSISDN,
PARTYID,
IMPLICIT_SEARCH_STRING,
date_key,
ROW_NUMBER() OVER
(PARTITION BY
MSISDN,
PARTYID
ORDER BY
MSISDN,
PARTYID,
IMPLICIT_SEARCH_STRING) RNUM
FROM PP_TMP_CUST_SERVICE_RECORD CSR)
START WITH
RNUM = 1
CONNECT BY PRIOR
MSISDN = MSISDN
AND PARTYID = PARTYID AND
PRIOR RNUM = RNUM - 1
GROUP BY MSISDN, PARTYID,date_key
This table is having more than 7 million data and is not coming out of query.
So, I'm thinking anyway we could do it in Datastage.
Suggestions please....
Thanks in Advance
Suresh
I have a scenario as below
input - oracle table
============
col1 col2
-------------
1 Sports
3 News
1 News
2 Weather
1 Weather
3 Sports
My output should have
===============
col1 col2
-------------------------
1 Sports News Weather
2 Weather
3 Sports News
Currently I'm doing it by a SQL query given below
-------------------------
SELECT /*+ INDEX(PP_TMP_CUST_SERVICE_RECORD PP_TMP_CUST_SERVICE_RECORD(MSISDN, PARTYID)) */
MSISDN,
PARTYID,
REPLACE(SUBSTR(MAX(SYS_CONNECT_BY_PATH(IMPLICIT_SEARCH_STRING, ':')), 2),':',' ') CONC_STRING,
date_key
FROM
(
SELECT
MSISDN,
PARTYID,
IMPLICIT_SEARCH_STRING,
date_key,
ROW_NUMBER() OVER
(PARTITION BY
MSISDN,
PARTYID
ORDER BY
MSISDN,
PARTYID,
IMPLICIT_SEARCH_STRING) RNUM
FROM PP_TMP_CUST_SERVICE_RECORD CSR)
START WITH
RNUM = 1
CONNECT BY PRIOR
MSISDN = MSISDN
AND PARTYID = PARTYID AND
PRIOR RNUM = RNUM - 1
GROUP BY MSISDN, PARTYID,date_key
This table is having more than 7 million data and is not coming out of query.
So, I'm thinking anyway we could do it in Datastage.
Suggestions please....
Thanks in Advance
Suresh