ok, this query works - but how do I see the null values for Type?

SELECT Job,type,
(InCredit+OutDebit) AS SumOfAmount
FROM AceData
WHERE type IS NOT NULL

should work

sorry I misread it

Try this ...

Select Job,[type1],[type2]
FROM (
SELECT Job,isnull(type, ''),
(InCredit+OutDebit) AS SumOfAmount
FROM AceData
)
ps PIVOT(
SUM (SumOfAmount)
FOR nYears IN([type1],[type2], '')
  ) AS pvt

Ss

ok, my fields are Job,Type,  InCredits and OutDebits and what I am saying is that if few types are null then it should add the values of the nulls.

I am getting typical errors in Server management studio
Select Job,[fee],[tax]
FROM (
SELECT Job,isnull(type, ''),
(InCredit+OutDebit) AS SumOfAmount
FROM AceData
)
ps PIVOT(
SUM (SumOfAmount)
FOR type IN([fee],[tax],'')
  ) AS pvt

Select Job,[fee],[tax],[nil]
FROM (
SELECT Job,isnull(type, 'nil'),
(InCredit+OutDebit) AS SumOfAmount
FROM AceData
)
ps PIVOT(
SUM (SumOfAmount)
FOR type IN([fee],[tax],[nil]
  ) AS pvt

Missed a bracket there


FOR type IN([fee],[tax],[nil])

Select Job,[fee],[tax],[nil]
FROM (
SELECT Job,isnull(type, 'nil'),
Isnull(InCredit,0)+isnull(OutDebit,0) AS SumOfAmount
FROM AceData
)
ps PIVOT(
SUM (SumOfAmount)
FOR type IN([fee],[tax],[nil])
  ) AS pvt

This is working fine, the only problem is that the column shows up even if there are no nulls, Is there a way to remove null column if all Type fields are filled? thanks



Select Job,[fee],[tax],[nil]
FROM (
SELECT Job,isnull(Type, 'nil') as xtype,
Isnull(InCredit,0)+isnull(OutDebit,0) AS SumOfAmount
FROM AceData
)
ps PIVOT(
SUM (SumOfAmount)
FOR xtype IN([fee],[tax],[nil])
  ) AS pvt

no directly. You will have to use dymamic SQL in that case. Check the below:

declare @strSQL varchar(max)
declare @cols varchar(max)


set @cols = stuff((SELECT distinct ', ' + isnull(Type, 'nil') FROM AceData for xml path('')), 1, 2, '')

set @strSQL = 'Select Job, ' + @cols
		FROM ( 
		SELECT Job,isnull(Type, ''nil'') as xtype, 
		Isnull(InCredit,0)+isnull(OutDebit,0) AS SumOfAmount 
		FROM AceData 
		) 
		ps PIVOT( 
			SUM (SumOfAmount) 
			FOR xtype IN(' + @cols + ') AS pvt '

exec(@strSQL)

Select allOpen in new window

what am I doing wrong? I get the following errors.

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'nil'.
Msg 105, Level 15, State 1, Line 15
Unclosed quotation mark after the character string '

exec(@strSQL)
'.

sorry some bugs in line 5

declare @strSQL varchar(max)
declare @cols varchar(max)


set @cols = stuff((SELECT distinct '], [' + isnull(Type, 'nil') FROM AceData for xml path('')), 1, 2, '') + ']'

set @strSQL = 'Select Job, ' + @cols
		FROM ( 
		SELECT Job,isnull(Type, ''nil'') as xtype, 
		Isnull(InCredit,0)+isnull(OutDebit,0) AS SumOfAmount 
		FROM AceData 
		) 
		ps PIVOT( 
			SUM (SumOfAmount) 
			FOR xtype IN(' + @cols + ') AS pvt '

exec(@strSQL)

Select allOpen in new window

does not run - still see errors - I am using sql 8 express

>> does not run - still see errors

What are the errors?

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'nil'.
Msg 105, Level 15, State 1, Line 15
Unclosed quotation mark after the character string '

exec(@strSQL)
'.

check line 8 here:

declare @strSQL varchar(max)
declare @cols varchar(max)


set @cols = stuff((SELECT distinct '], [' + isnull(Type, 'nil') FROM AceData for xml path('')), 1, 2, '') + ']'

set @strSQL = 'Select Job, ' + @cols
		+ ' FROM ( 
		SELECT Job,isnull(Type, ''nil'') as xtype, 
		Isnull(InCredit,0)+isnull(OutDebit,0) AS SumOfAmount 
		FROM AceData 
		) 
		ps PIVOT( 
			SUM (SumOfAmount) 
			FOR xtype IN(' + @cols + ') AS pvt '

exec(@strSQL)

Select allOpen in new window

may be the last one - thanks

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'AS'.

there was a missing bracket in line 15. This should work:

...                  FOR xtype IN(' + @cols + ')) AS pvt '

thanks