Reputation: 827
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
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