SQL Server 2008 PIVOT

[Duran Harris]

Hi guys,

I have a requirement to create a cross-tab query in MS SQL Server 2008....After reading up a bit I started trying to do this with the PIVOT operator.
The cross-tab query is tod display a table of exchange rates
Let me show you the table data that I am operating on:

SELECT ic.currencyName,icm.baseCurrency
                     ,icm.SecondCurrency,icm.ExchangeRate 
                     FROM InteractiveCurrencyMappings  icm
                     INNER JOIN InteractiveCurrency ic ON icm.BaseCurrency=ic.Id

yields(although the exchange rates are backward):


Then I use the following to do the pivot:

DECLARE @query VARCHAR(2000)
DECLARE @cols VARCHAR(2000)
DECLARE @unaliasedCols VARCHAR(2000)
SELECT  @cols = COALESCE(@cols + ',[' + CAST(id AS VARCHAR) + '] as '+currencyCode,
                         '[' + CAST(id AS VARCHAR) + '] as '+currencyCode)
FROM    InteractiveCurrency
SELECT  @unaliasedCols = COALESCE(@unaliasedCols + ',[' + CAST(id AS VARCHAR) + ']',
                         '[' + CAST(id AS VARCHAR) + ']')
FROM    InteractiveCurrency


SET @query = 'SELECT '+@cols+',cur.currencyCode as SecondCurrency FROM
	(SELECT * FROM InteractiveCurrencyMappings 
		PIVOT(
			AVG(ExchangeRate) FOR secondCurrency IN ('+@unaliasedCols+')		 )
		 as pvt
	
	 )as result INNER JOIN InteractiveCurrency cur ON cur.id=result.baseCurrency'
	
	 EXECUTE(@query)

which yields:



The problem is that I dont want values repeated in the secondCurrency column....They should all appear on the same row for any given 'secondCurrency'
Please assist?
Thanks...

[Duran Harris]

###SOLVED