Yes, you can use pivots !
Have a looks at this example:
CREATE TABLE #Prices (ProductName NVARCHAR(30), Price MONEY, UpdateDate DATETIME)
INSERT INTO #Prices (ProductName, Price, UpdateDate)
VALUES('ABC',2.20,'2012-01-03')
INSERT INTO #Prices (ProductName, Price, UpdateDate)
VALUES('ABC',2.85,'2012-03-07')
INSERT INTO #Prices (ProductName, Price, UpdateDate)
VALUES('ABC',3.01,'2012-05-13')
INSERT INTO #Prices (ProductName, Price, UpdateDate)
VALUES('ABC',3.05,'2012-07-15')
INSERT INTO #Prices (ProductName, Price, UpdateDate)
VALUES('DEF',13.15,'2012-01-05')
INSERT INTO #Prices (ProductName, Price, UpdateDate)
VALUES('DEF',12.95,'2012-03-07')
INSERT INTO #Prices (ProductName, Price, UpdateDate)
VALUES('DEF',13.09,'2012-05-10')
INSERT INTO #Prices (ProductName, Price, UpdateDate)
VALUES('GHI',5.19,'2012-01-03')
INSERT INTO #Prices (ProductName, Price, UpdateDate)
VALUES('GHI',5.88,'2012-07-15')
DECLARE @cols NVARCHAR(200)
DECLARE @dt NVARCHAR(1000)
DECLARE @pt NVARCHAR(2000)
SET @cols = STUFF((SELECT DISTINCT '],[' + CONVERT(NVARCHAR(10),P.[UpdateDate],120)
FROM [#Prices] AS P
ORDER BY '],[' + CONVERT(NVARCHAR(10),P.[UpdateDate],120)
FOR XML PATH('')),1,2,'') + ']'
SET @dt = 'SELECT * ' +
'FROM #Prices '
SET @pt = 'SELECT [ProductName], ' + @cols + ' ' +
'FROM (' + @dt + ') AS DT ' +
'PIVOT(MAX([Price]) FOR [UpdateDate] IN (' + @cols + ')) AS PT ' +
'ORDER BY [ProductName]'
EXEC (@pt)
DROP TABLE #Prices
Results:
ProductName | 2012-01-03 | 2012-01-05 | 2012-03-07 | 2012-05-10 | 2012-05-13 | 2012-07-15 |
---|
ABC | 2.20 | NULL | 2.85 | NULL | 3.01 | 3.05 |
DEF | NULL | 13.15 | 12.95 | 13.09 | NULL | NULL |
GHI | 5.19 | NULL | NULL | NULL | NULL | 5.88 |
The rest belongs to you!