Converting rows to columns in Teradata sql

[somu_june]

Hi All,

I'm  trying to pivot the data in below table to columns.

I have a member table and it contains information like below

MEMBER_ID|ID|VALUE

123|456|TAKEN

123|555|NOT TAKEN

123|666|UNKNOWN

123|888|20100312

123|888|20100415

For the same member 123 and witth different Id  values will provide different information like ID = 456 will provide about test information and ID = 888 will provide date information and the values are in VALUE field

So for the particular member 123  I looking for an output like below  with all the information with changing id value and its corresponding values

Output

MEMBER_ID|TEST_ID1|TEST_ID2|TEST_ID3|DATE1|DATE2

123|TAKEN|NOT TAKEN|UNKNOWN|20100312|20100415

Note: I'm just using pipe in above example to seperate fields.

Thanks,

Raju

[Qaisar_Kiani]

To understand the problem completely and to suggest a solution, I guess its important to know how many distinct ID values can possibly be for a MEMBER, and how many distinct possible VALUE can exist for each ID against each member?

Because the number of columns in the pivot result will depend on these values.

If these values are limited then a SQL logic with fixed number of columns will do the job otherwise a recursive SQL or a procedure will be the way to go.