SQL Server : Pivot table

By : Galaxy
Source: Stackoverflow.com
Question!
create PROC [dbo].[Sample]    
 @fromDate datetime, @toDate datetime, @office varchar(30) 
AS 
declare @char varchar(200)
DECLARE @Temp TABLE (ID int, Name varchar(50), Countt int, Reason varchar(20))

INSERT INTo @Temp (ID, Name, Countt, Reason)
SELECT DD.ID, O.Name, Count(DD.Reason) Countt, convert(varchar,DD.Reason) Reason FROM samp1 AS DD       
INNER JOIN samp3 AS O ON O.ID = DD.ID

select  @char = coalesce(@char + ',', '') + reason from @Temp

select * 
FROM
(
    select  distinct ID, Name, Reason, sum(Countt) as Countt from @Temp group by Name, Reason, ID
)P

PIVOT
(
    SUM(Countt)
    FOR  Reason
    IN (select @char)
) AS pvt



Error: Msg 156, Level 15, State 1, Procedure Sample, Line 45
Incorrect syntax near the keyword 'select'.

can anybody help me

By : Galaxy


Answers
SELECT @char = COALESCE(@char   ',['   reason   ']', '['   reason   ']')  
FROM @Temp  

DECLARE @pvtQuery VARCHAR(2500)  

SET @pvtQuery =  
'  
SELECT ID, Name, '   @char   '  
FROM  
(  
    SELECT DISTINCT ID, Name, Reason, SUM(Countt) AS Countt  
    FROM @Temp  
    GROUP BY Name, Reason, ID  
) P  
PIVOT  
(  
    SUM(Countt)  
    FOR Reason  
    IN ('   @char   ')  
) AS pvt  
'  

EXEC (@pvtQuery)  
By : Gzo


This video can help you solving your question :)
By: admin