Approach required for this Pivoting example.

pranay · Post by **pranay** » Tue Jul 01, 2008 11:33 am

Hi All,
I need to implement something like this : I have a table that contains empid, monthyear and salary. the primary key is the combination of empid and monthyear. Here's the data in the table
101 Jan 07 5000
101 Feb 07 4850
101 Mar 07 4850
101 Apr 07 4850
101 May 07 4850
101 July 07 4850
102 Feb 07 4850
102 Mar 07 6050
103 Feb 07 4050

for all the distinct rows in monthyear column it should create seperate columns and display the output as given below.
The output should be
Jan07 Feb07 Mar07 Apr07 May07 June07 July07
101 5000 4850 4850 4850 4850 ---- 4850
102 --- 4850 6050 ---- ---- ---- ----
103 ----- 4050 ----- --- ---- ---- ----

Can any one give me the approach for this. Thanks in advance. Pranay

rameshrr3 · Post by **rameshrr3** » Tue Jul 01, 2008 12:03 pm

easier to process using rdbms stored procedures.

pranay · Post by **pranay** » Tue Jul 01, 2008 12:37 pm

Hi ramesh, Could you please send me the stored proc for the same.

rameshrr3 wrote:easier to process using rdbms stored procedures.

chulett · Post by **chulett** » Tue Jul 01, 2008 12:53 pm

:D He wasn't offering to build one for you, just stating his preference as to the approach to take to solve this.

rameshrr3 · Post by **rameshrr3** » Tue Jul 01, 2008 1:01 pm

At DSXchange we dont guarantee that solutions to problems will be given , helpful suggestions and hints are somethings that you can look for, i can however send you a dsx file for a vertical pivot operation that is only slightly different from your case-provided you give me your email id.

Writing a procedure in PL/SQl for your case should be fairly straightforward,more so if you use intermediate tables- however the target file structure is what i would call a sparse format - leaidng to unecessary disk space consumption if left uncompressed. How many columns do you plan to have in the file ? 1 field for every month of every year?
Or 12 month fields for every file and 1 file per year?

alternatively you can sort your input file by month and write to a folder stage with month as output key column, that way you will end up with 1 file for every month, after which you can join fields based on key value and concatenate.

On parallel extender you may need to explore options on your restructure stages.

pranay · Post by **pranay** » Tue Jul 01, 2008 1:40 pm

Thanks Ramesh for the valuable suggestions.
you can send the dsx to me at kpranay81@gmail.com.
Also i need to have one output file per year with 12 month fields.

rameshrr3 wrote:At DSXchange we dont guarantee that solutions to problems will be given , helpful suggestions and hints are somethings that you can look for, i can however send you a dsx file for a vertical pivot operation that is only slightly different from your case-provided you give me your email id.

Writing a procedure in PL/SQl for your case should be fairly straightforward,more so if you use intermediate tables- however the target file structure is what i would call a sparse format - leaidng to unecessary disk space consumption if left uncompressed. How many columns do you plan to have in the file ? 1 field for every month of every year?
Or 12 month fields for every file and 1 file per year?

alternatively you can sort your input file by month and write to a folder stage with month as output key column, that way you will end up with 1 file for every month, after which you can join fields based on key value and concatenate.

On parallel extender you may need to explore options on your restructure stages.

ray.wurlod · Post by **ray.wurlod** » Tue Jul 01, 2008 4:24 pm

This is sounding more and more like a commercial arrangement. If you want Ramesh (or anyone else) to work for you how much are you prepared to pay for it?

rameshrr3 · Post by **rameshrr3** » Tue Jul 01, 2008 6:38 pm

Hi ramesh, Could you please send me the stored proc for the same.

, well no commercial engagements yet ..i could also post the 'dsx' or job report on dsxchange if there was place to upload it.