I didn't answer that very well. Let me rephrase.

Your initial question is whether you can PIVOT without aggregation. The answer is no: PIVOT requires aggregation. That being said - you can use pivot to get what you want, assuming you "pivot" smartly. The trick is to make sure that the pivoting occurs on a group that would only return one row.

Meaning - use aggregation, even if it ultimately does "nothing".

In your example, this would yield the right result:

select product_id, USA,UK,SGP

from

(

select product_id,

countrycode,

alternateproductid

from mytable

) topvt

PIVOT

(

max(alternateproductid)

FOR countrycode in ([usa],[uk],[sgp])

) pvt

This might become problematic if you should ever get TWO alt. product ID's for a given country. But that's the "being smart" is about. Make sure that it only yields one per group.

MDX is also about aggregation. That's the purpose of a cube.