PIVOT in sql server 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

PIVOT in sql server 2005

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-02-12 : 21:53:35

snehalata writes "i want to generate a cross tab report using PIVOT in sql server 2005

i have created a CTE as follows

WITH ABC(RowID, DisplayName, Type,Value,ValueSign)
AS
(
SELECT A.RowID,
A.DisplayName,
C.Type,
C.Value,
B.ValueSign
FROM RepRowInfo A,
RepRowDetail B,
#ReportingNumbers C
WHERE A.RowId = B.RowId
AND A.FundID = @FundID
AND A.FundReportID = @FundreportID
AND A.SectionID = @SectionID
AND B.HeadID = C.HeadID
)

and then select statement using PIVOT operator as follows , if i use CASE in aggregate function as mentioned in the query i get error, how can i write this query ?

SELECT pvt.RowID,
Period = [PERIOD] ,
MTD = [MTD] ,
QTD = [QTD] ,
YTD = [YTD] ,
ITD = [ITD]
FROM ABC
PIVOT
(
SUM(CASE WHEN ValueSign = '+' THEN Value ELSE -1 * Value END )
--SUM(Value)
FOR Type IN ([PERIOD], [MTD], [QTD],[YTD],[ITD])
) AS pvt
ORDER BY pvt.RowID"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-13 : 01:07:38

Try this
SUM(CAST(ValueSign + CAST(Value AS VARCHAR)) AS MONEY)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources