Column to rows

[turash]

I am getting data in sequential file as follows
Input

• IssuingCompanyCd,013550
  PolOfficeCd,000130
  PolicyNo,WR101
  CertificateNo,101010
  RenlCertNo,000000
  EndorsementEffDt,01012008
  EndorsementSqn,1

Want Convert Output Sequential File like the Following
Output
IssuingCompanyCd,PolOfficeCd,PolicyNo,CertificateNo,RenlCertNo,EndorsementEffDt,EndorsementSqn
013550,000130,WR101,101010,000000,01012008,1

First row is as header in this case

I have tried use pivot, Row Merge without much of luck

Thanks In advance,
Tushar

[chulett]

You would need to use stage variables to (ultimately) build a concatenated record (including field delimiters) and then only write it out when you have a 'complete record' as one long varchar.

That could be when you read the "EndorsementSqn" record or after you've read 7 records, depending on if you always get all the pieces or not.

[turash]

I am trying to make 2 records out of the 7 records

1st Record contains first columns of these 7 rows
2nd Record contains second columns of these 7 rows.

Could you please give me more details?

You would need to use stage variables to (ultimately) build a concatenated record (including field delimiters) and then only write it out when you have a 'complete record' as one long varchar.

That could be when you read the "EndorsementSqn" record or after you've read 7 records, depending on if you always get all the pieces or not.

[chulett]

You are trying to make 2 records out of each set of 7 records? Or do you only need to produce the 'heading' row once?

[turash]

You are trying to make 2 records out of each set of 7 records? Or do you only need to produce the 'heading' row once?

Lets talk about multi value flat file
Input
IssuingCompanyCd,013550
PolOfficeCd,000130
PolicyNo,WR101
CertificateNo,101010
RenlCertNo,000000
EndorsementEffDt,01012008
EndorsementSqn,1

IssuingCompanyCd,013551
PolOfficeCd,000131
PolicyNo,WR102
CertificateNo,101012
RenlCertNo,000002
EndorsementEffDt,01012009
EndorsementSqn,2

Output
IssuingCompanyCd,PolOfficeCd,PolicyNo,CertificateNo,RenlCertNo,EndorsementEffDt,EndorsementSqn
013550,000130,WR101,101010,000000,01012008,1
013551,000131,WR102,101012,000002,01012009,2

I have to create the header only once and create two more rows with its values.

[ray.wurlod]

Code: Select all

If @OUTROWNUM = 1 Then InLink.Field001 Else InLink.Field002

Preserve these values in stage variabales. Constrain your output such that

Code: Select all

InLink.Field001 = 'EndorsementSqn'

[chulett]

If these header names are known in advance, just name your output columns appropriately and ignore them in the data.

[sachin1]

Hello,

In hurry i tried working for your doubt.

job design like
Input file ----->Transformer------>(2 output)


you will require two stage variables.

one colvalue and other valuecolumns.(stage variables).

your two input columns will be colname and colvalue.

define for colvalue -->colvalue :',': DSLink11.colname.

define for valuecolumns-->If mod(@INROWNUM,7)=0 Then ' ' Else trim(valuecolumns):',': DSLink11.colvalueinp.

in first output put the value for target column as trim(colvalue,',') and in constraint put (@INROWNUM=7),because you have 7 columns.


in second output put value for target column as trim(valuecolumns,',','l') and in constraint put (mod(@INROWNUM,7)=6).

file file in output should be used in second output in append mode.

hope that works for you.
thanks.

[sachin1]

your two input columns will be colname and colvalueinp.