CODEWITHSUNDEEP
sqlsql-server-2008pivot
Data Engineer
Data Engineer

Reputation: 827

Display Columns into Rows

I'm using SQL Server and I need to display the information on the query by columns. Currently, the information is displayed as:

ProjectID   FiscYr  Period  Amt 
4231        2015    1       100 
4231        2015    1       820 
***         ***    ***      *** 
***         ***    ***      ***
4231        2015    12      733

needs to be formatted as:

ProjectID   FiscYr  Period 01   Period 02   *** ***  Period 12
4231        2015         100         820    *** ***  733

Existing query is:

SELECT        substring([project],11,4) as ProjectID
               ,substring([fiscalno],1,4) as FiscYr
               ,substring([fiscalno],5,2) as Period
               ,sum([amount]) as Amt
FROM [dbo].[PJTran] 
WHERE (((pjtran.gl_Acct) Like '12%' or (pjtran.gl_Acct) Like '13%')) 
group by substring([project],11,4),substring(fiscalno,1,4),substring([fiscalno],5,2);

Upvotes: 1

Views: 87

Answers (1)

jpw
jpw

Reputation: 44921

Wrap your query in a common table expression and then apply the pivot operator:

WITH CTE AS (
    SELECT        
         SUBSTRING([project],11,4) AS ProjectID
       , SUBSTRING([fiscalno],1,4) AS FiscYr
       ,'Period ' + SUBSTRING([fiscalno],5,2) AS Period
       , Amount
    FROM [dbo].[PJTran] 
    WHERE pjtran.gl_Acct LIKE '1[23]%' 
)

SELECT * FROM CTE
PIVOT (SUM(Amount) FOR Period IN (
    [Period 1],[Period 2], [Period 3], [Period 4],
    [Period 5],[Period 6], [Period 7], [Period 8],
    [Period 9],[Period 10],[Period 11],[Period 12]
    )
) p;

Upvotes: 1

Related Questions