Which column is NULL?

If a country code is mentioned once in any row - it should show in the column list, and put NULL next to the ones that don't have a value. If the country is not mentioned any any physical row - no column will show up. Of course - if you HAVE a predetermined list of countries, then using UNION to "insert" them in would make the column appear.

If you don't want to see NULLs then use the ISNULL or COALESCE functions to take that out.

select product_id,

isnull(pvt.USA) USA,

Isnull(pvt.UK) UK,

Isnull(pvt.SGP) SGP

from (

select product_id,

countrycode,

alternateproductid

from mytable

) topvt

PIVOT

(

max(alternateproductid)

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

) pvt