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
Concat values into single row
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 81
- Joined: Mon Nov 21, 2005 4:17 am
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 81
- Joined: Mon Nov 21, 2005 4:17 am
- Location: Sydney, Australia
- Contact:
Thanks Ray !!!
I'm able achieve it very easily.
viewtopic.php?t=97681&start=0&sid=5e14c ... f8d3276cae
This post had helped me.
I'm able achieve it very easily.
viewtopic.php?t=97681&start=0&sid=5e14c ... f8d3276cae
This post had helped me.
SURESH NARASIMHA