thanks much. that hepled.

How Can I generate " create table" script if table does not exist in db2 ?

Thank you so much amelvin, and Antares686. That worked.

Thanks to rdfozz, and rbinnington for your response.

I do not want to group by col4.

Actually I will get multiple col4 values for the same value in col1. we can assume col1 as person name, and col4 as...

Let me be more clear.

The query was like this

SELECT col1,col2,col3,

SUM(CASE col4 WHEN 'first' THEN col5 ELSE 0 END) AS first,

SUM(CASE col4 WHEN 'second' THEN col5 ELSE 0 END) AS second,

...

YES, BUT 'AS' ALSO SHOULD @val AND i SHOULD LOOP THROUGH THE LIST OF VALUES FOR VAL IN THE SAME SELECT STATEMENT.

IS THAT POSSIBLE?