Pivot dynamic columns to rows

pratapsriram · Post by **pratapsriram** » Tue Aug 21, 2012 12:36 pm

I have a unique problem.
Securities data looks like this:
Credit Family BB Internal PP or CUSIP
1166 AVENUE OF THE AMERICAS COMMERICAL MTG TRUST 682439AC8
1166 AVENUE OF THE AMERICAS COMMERICAL MTG TRUST 682439AE4
1166 AVENUE OF THE AMERICAS COMMERICAL MTG TRUST 682439AJ3
1166 AVENUE OF THE AMERICAS COMMERICAL MTG TRUST 682439AN4
1166 AVENUE OF THE AMERICAS COMMERICAL MTG TRUST 682439AU8
125 HOME LOAN OWNER TRUST 68240MAE8
1345 AVE OF THE AMERICAS & PARK AVE PLAZA TRUST 68275CAC2
1345 AVE OF THE AMERICAS & PARK AVE PLAZA TRUST 68275CAD0
1345 AVE OF THE AMERICAS & PARK AVE PLAZA TRUST 68275CAE8
1345 AVE OF THE AMERICAS & PARK AVE PLAZA TRUST 68275CAF5
1345 AVE OF THE AMERICAS & PARK AVE PLAZA TRUST 68275CAG3
1345 AVE OF THE AMERICAS & PARK AVE PLAZA TRUST 68275CAH1

I have changed it to this format:

Credit Family,BB Internal PP or CUSIP,BB Internal PP or CUSIP,BB Internal PP or CUSIP,BB Internal PP or CUSIP,BB Internal PP or CUSIP,BB Internal PP or CUSIP
1166 AVENUE OF THE AMERICAS COMMERICAL MTG TRUST,682439AC8,682439AE4,682439AJ3,682439AN4,682439AU8,
125 HOME LOAN OWNER TRUST,68240MAE8,,,,,
1345 AVE OF THE AMERICAS & PARK AVE PLAZA TRUST,68275CAC2,68275CAD0,68275CAE8,68275CAF5,68275CAG3,68275CAH1

The final output format, after doing a pivot, looks like this:
Value_field
1166 AVENUE OF THE AMERICAS COMMERICAL MTG TRUST
682439AC8
682439AE4
682439AJ3
682439AN4
682439AU8

125 HOME LOAN OWNER TRUST
68240MAE8

1345 AVE OF THE AMERICAS & PARK AVE PLAZA TRUST
68275CAC2
68275CAD0
68275CAE8
68275CAF5
68275CAG3
68275CAH1

Lastly I will delete the empty lines so there is no blank values. This is the format required for loading into another tool.
But this means the number of columns is varying. I want to know what is the maximum number of columns we can define so that once we define it and making use of the incomplete column we will be ok. But I already know that for some Credit Family like US Treasury, the number of values can range into 500 rows. So can we give 1000 columns in the sequential read file to achieve this?
Or does anyone have another approach for solving this?

Thanks,
Sreeram

ray.wurlod · Post by **ray.wurlod** » Tue Aug 21, 2012 4:34 pm

This is not actually a pivot.

Do it with stage variables and you won't have any issues with the number of rows.

Output the company name only when it changes.

Kryt0n · Post by **Kryt0n** » Tue Aug 21, 2012 4:35 pm

You appear to be trying to do two pivots just to remove data?

Why doesn't the first row for each security output the name followed by reference number and all subsequent rows just output the reference number?
You will need suitable partitioning and sorting but sounds simpler

ray.wurlod · Post by **ray.wurlod** » Tue Aug 21, 2012 4:41 pm

It's a server job, so there's no partitioning.

Kryt0n · Post by **Kryt0n** » Tue Aug 21, 2012 7:03 pm

Maybe one day I'll look at the forum first...

pratapsriram · Post by **pratapsriram** » Wed Aug 22, 2012 7:17 am

This is what I understood - Use stage variables to construct the line "Credit Family Name" with the "\n" and append with the Security identifier when the name changes so that a new line with the Credit family name appears...