Hai,
That looks great but it gives me all nulls if the column doesnot have alternative identifirer for the product.
how do i handle that.
Very good trick. but does that realy work with nulls aswell.
For posterity's sake, please allow me to chime in here...
I think what bhushanvinay is saying is that since there is no real aggregation going on here, he is not getting one row with all the AlternateProductID for each product. Instead, referring to his example data, he would get three rows with nulls for each of the CountryCodes that do not have a corresponding AlternateProductID. The Pivot function would work, but the values would be staggered across each of the columns and look something like this:
ProductID USA UK SGP
------------------------------------------------------------------------
XDell020458 XDell20499-001 NULL NULL
XDell020458 NULL XDell20489-002 NULL
XDell020458 NULL NULL XDell20489-003
So, the code Matt provided is a tad incomplete. What you also need is to get the "max" value of each of the CountryCodes returned by the Pivot function, which, as Matt eluded to earlier, will always return one value, so max() serves as a dummy function in this sense:
select product_id, max(USA),max(UK),max(SGP)
from
(
select product_id,
countrycode,
alternateproductid
from mytable
) topvt
PIVOT
(
max(alternateproductid)
FOR countrycode in ([usa],[uk],[sgp])
) pvt
group by Product_ID
So, when you are pivoting without aggregation, you still must feign an aggregation in the outermost select statement to get one row.