Concat values into single row

[suresh.narasimha]

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

[ray.wurlod]

Sure can. Search DSXchange for vertical pivot - there are at least three different ways available in server jobs. Which to use depends on your particular circumstances, in particular how many distinct values there might be per row and whether this number is fixed or variable.

[suresh.narasimha]

Thanks Ray !!!

I'm able achieve it very easily.

viewtopic.php?t=97681&start=0&sid=5e14c ... f8d3276cae

This post had helped me.