I have data in one column which is separated by comma which look like below
1st row
MTCN,Rec Date,Payee Name,Sender Name,Rec Acct,TID,OPID,NETID,Orig-Pay-NAID,Cnl-Acct,TID,OPID, PRD,Prin,Charges,Tot fee,Gross,Del Svc,Comment
2nd row
`973XXXX80,03-06-13,JOSE ALFREDO\GUTIERREZ VASQUEZ,JOSE ALFREDO\GUTIERREZ VASQUEZ,VST121707,AE91,001,00690,MXV01,VKC358297,W244. ........
I need to ignore 1 row and from 2nd row i need to split data in to multiple data in separate columns
Split one column into multiple columns
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 4
- Joined: Tue Jan 30, 2007 4:29 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Read with a Sequential File stage with First Line is Column Names set to True. Read the whole row into a single column, whose data type is a sufficiently large VarChar.
Parse the data using a Column Import stage or a Transfomer stage using Field() functions.
Parse the data using a Column Import stage or a Transfomer stage using Field() functions.
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.
-
- Participant
- Posts: 4
- Joined: Tue Jan 30, 2007 4:29 am
Split one column into multiple columns
Ray,
as said in my first line.. data is there in table with one column and data residing is delimited by comma, i tried to use Filed function in transformer to split one column into multiple columns but i need to ignore the one row which has column names.. i hope you got what i'm saying.
as said in my first line.. data is there in table with one column and data residing is delimited by comma, i tried to use Filed function in transformer to split one column into multiple columns but i need to ignore the one row which has column names.. i hope you got what i'm saying.
-
- Participant
- Posts: 117
- Joined: Wed Feb 06, 2013 9:24 am
- Location: Chennai,TN, India
-
- Participant
- Posts: 4
- Joined: Tue Jan 30, 2007 4:29 am
Split one column into multiple columns
even if i ensure the first row will always be headers, then how do i query to select all rows except first row, is there any db2 sql to do so?? i didn't find any in forums. plz provide me one sql where i can exclude first row.
-
- Participant
- Posts: 117
- Joined: Wed Feb 06, 2013 9:24 am
- Location: Chennai,TN, India
Re: Split one column into multiple columns
Use a nested select statement where you add a row number:
select * from
(select key, row_number() over(partition by 1) as rownum from table1)
where rownum > 1;
select * from
(select key, row_number() over(partition by 1) as rownum from table1)
where rownum > 1;