With the help of pivot table in sql server 2005 we can do so

I agree with this answer..and also we can do the pivot 
transaformation in SSIS.

creating pivot table:
Columns as Rows

below statement will give as it is data

select
    PS.Name, P.Color, PIn.Quantity
  from Production.Product P
  inner join Production.ProductSubcategory PS
    on PS.ProductSubcategoryID = P.ProductSubcategoryID
  left join Production.ProductInventory PIn
    on P.ProductID = PIn.ProductID
This statement will change the columns as rows

select
  *
from
(
  select
    PS.Name, P.Color, PIn.Quantity
  from Production.Product P
  inner join Production.ProductSubcategory PS
    on PS.ProductSubcategoryID = P.ProductSubcategoryID
  left join Production.ProductInventory PIn
    on P.ProductID = PIn.ProductID
) DataTable
PIVOT
(
  SUM(Quantity)
  FOR Color
  IN (
    [Black],[Blue],[Grey],[Multi],[Red],
    [Silver],[Silver/Black],[White],[Yellow]
  )
) PivotTable