Select statement cannot be used directly to create headers. Here is how it can be done by creating a list of columns and then creating a PIVOT string which include those columns
DECLARE @ColStr VARCHAR(500),
@SqlStr VARCHAR(MAX);
-- Creating Column String
WITH col_str AS
(SELECT DISTINCT reason FROM Payment)
SELECT @ColStr=ISNULL(@ColStr+',','')+Reason FROM col_str;
-- Creating pivot query
SET @SqlStr ='SELECT paymentID, '+@ColStr+'
FROM (SELECT paymentID,
CASE receivedType WHEN ''Dr'' THEN Amount ELSE -1 * Amount END Amount, Reason
FROM Payment) AS SourceTable
PIVOT ( Sum(Amount)
FOR Reason IN ('+@ColStr+') ) AS PivotTable '
-- Execute Query
EXEC (@SqlStr);
~ Lokesh Vij
Link to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter