Dynamic pivot table with multiple columns in sql server

Question

I am trying to pivot table DYNAMICALLY but couldn't get the desired result. Here is the code to create a table

 create table Report
 (
 deck char(3),
 Jib_in float,
 rev int,
 rev_insight int,
 jib_out float,
 creation int
 )

 insert into Report  values
 ('A_1',0.345,0,0,1.23,20140212),
 ('B_2',0.456,0,4,2.34,20140215),
 ('C_3',0.554,0,6,0.45,20140217),
 ('D_4',0.231,0,8,7.98,20140222),
 ('E_5',0.453,0,0,5.67,20140219),
 ('F_6',0.344,0,3,7.23,20140223)'

Code written so far.... this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()

 DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
 SET @columns = N'';
 SELECT @columns += N', p.' + QUOTENAME(deck)
 FROM (SELECT p.deck FROM dbo.report AS p
 GROUP BY p.deck) AS x;
 SET @sql = N'
 SELECT ' + STUFF(@columns, 1, 2, '') + '
 FROM
 (
 SELECT p.deck, p.jib_in
 FROM dbo.report AS p
 ) AS j
 PIVOT
 (
 SUM(jib_in) FOR deck IN ('
 + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
 + ')
 ) AS p;';
 PRINT @sql;
 EXEC sp_executesql @sql;

I need all the columns to be pivoted and show on the pivoted table. any help would be appreciated. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!! I know there are other ways please feel free to mention if there is any other way to get this right.

Solution

Please use this (If you are getting Collation issue, please change all the 3 INT datatypes):

STATIC code:

SELECT HEADER, [A_1],[B_2],[C_3],[D_4],[E_5],[F_6]        
FROM    
(SELECT DECK,HEADER, VALUE FROM REPORT   
    UNPIVOT      
    ( 
    VALUE FOR HEADER IN ([JIB_IN],[REV],[REV_INSIGHT],[JIB_OUT],[CREATION])  
    ) UNPIV
) SRC     
PIVOT    
(
    SUM(VALUE) 
    FOR DECK IN ([A_1],[B_2],[C_3],[D_4],[E_5],[F_6]) 
) PIV

Using Dynamic SQL:

DECLARE @COLSUNPIVOT AS NVARCHAR(MAX),
@QUERY  AS NVARCHAR(MAX),
@COLSPIVOT AS  NVARCHAR(MAX)

SELECT @COLSUNPIVOT = STUFF((SELECT ','+QUOTENAME(C.NAME)
FROM SYS.COLUMNS AS C
WHERE C.OBJECT_ID = OBJECT_ID('REPORT') AND C.NAME <> 'DECK'
FOR XML PATH('')), 1, 1, '')

SELECT @COLSPIVOT = STUFF((SELECT  ',' + QUOTENAME(DECK)
FROM REPORT T FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

SET @QUERY 
= 'SELECT HEADER, '+@COLSPIVOT+'
FROM
(
SELECT DECK,HEADER,VALUE FROM REPORT
UNPIVOT
(
  VALUE FOR HEADER IN ('+@COLSUNPIVOT+')
) UNPIV
) SRC
PIVOT
(
SUM(VALUE)
FOR DECK IN ('+@COLSPIVOT+')
) PIV'

EXEC(@QUERY)