Multiple columns from multiple rows

thespecialist · Post by **thespecialist** » Fri Aug 12, 2005 11:28 am

Hi,

I am trying to write a user defined SQL to extract a table which has multiple records for the fields i want as key. I need to get multiple columns from these rows based on the value in a particular field.

for eg: i have

PID1 PID2 type code

20 123 1 sdte
20 123 2 wert
20 123 3 rtyr
21 145 2 jhgt
21 145 3 swer

what i want is
PID1 PID2 code1(type=1) code2(type=2) code3 (type=3)
20 123 sdte wert rtyr
21 145 null jhgt swer

in the above table the fields code1,code2,code3 has to be populated depending on the value in the field 'type'

Please help me with this.

Thank you.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Aug 12, 2005 12:05 pm

You can use logic in transform to populate any of the 3 cols.

You can then use hash files to obtain them in one row.

thespecialist · Post by **thespecialist** » Fri Aug 12, 2005 2:55 pm

Thanks Sainath.
actually i have to match 5 codes, so by using hash files i will end up doing a number of lookups and the requirement is to do using a user defined sql.
Anyway i got the solution.
Thanks again.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Aug 12, 2005 3:58 pm

Can you post your solution so others having similar requirement may find it useful.