Inverting columns to row header in sql

I have a table

ID  Productcode  AttName    Attval
1   IPHONE       Color  Black
2   IPHONE       Bluetooth  Yes
3   IPHONE       Camera Yes
4   MOTV         Color  Silver
5   MOTV         Bluetooth  No
6   MOTV         Camera No

I need to generate

IPHONE  Color   Black   Bluetooth   Yes Camera  Yes
MOTV    Color   Silver  Bluetooth   No  Camera  No

Is this possible using pivot or any other method?

We are using sql 2005 Thanks

Scripts

Create TAble TempInve(ID INT Identity(1,1),Productcode VARCHAR(40),AttName VARCHAR(40),Attval VARCHAR(50))
INSERT INTO TempInve
SELECT 'IPHONE','Color','Black' UNION ALL 
SELECT 'IPHONE','Bluetooth','Yes' UNION ALL
SELECT 'IPHONE','Camera','Yes' 


INSERT INTO TempInve
SELECT 'MOTV','Color','Silver' UNION ALL 
SELECT 'MOTV','Bluetooth','No' UNION ALL
SELECT 开发者_StackOverflow社区'MOTV','Camera','No' 

---

for IPHONE:

DECLARE @result VARCHAR(MAX) = 'IPHONE '

SELECT @result += AttName + ' ' + Attval + ' '
FROM TempInve
WHERE productCode = 'IPHONE'

SELECT @result

if you use cursor and select all distinct productCode, you can easily do this for all...

---

SELECT Productcode, 
       MAX(CASE WHEN AttName='Color' THEN Attval END) AS Attval,
       MAX(CASE WHEN AttName='Bluetooth' THEN Attval END) AS Bluetooth,
       MAX(CASE WHEN AttName='Camera' THEN Attval END) AS Camera
FROM TempInve
GROUP BY Productcode

Or

;WITH T
     AS (SELECT Productcode,
                AttName,
                Attval
         FROM   TempInve)
SELECT *
FROM   T PIVOT( max (Attval) FOR AttName IN ( [Color], [Bluetooth], [Camera] ) )
       AS pvt  

---

If your attribute names keep changing or are not static, then you can use this

declare @sql nvarchar(max);

-- generate the column names
select @sql = coalesce(@sql + ',', '') + QuoteName(AttName)
from (select distinct AttName from TempInve) T;

-- replace the column names into the generic PIVOT form
set @sql = REPLACE('
select ProductCode, :columns:
from (select Productcode, AttName, Attval From TempInve) p
pivot (max(attval) for attname in (:columns:)) pv',
':columns:', @sql)

-- execute for the results
exec (@sql)