[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-04-30
20
Medium Priority
?
302 Views
Last Modified: 2012-05-11
Select Job,[type1],[type2]
FROM (
SELECT Job,type,
(InCredit+OutDebit) AS SumOfAmount
FROM AceData
)
ps PIVOT(
SUM (SumOfAmount)
FOR nYears IN([type1],[type2])
  ) AS pvt

my table is simple,
it has columns
 date,job,incredit,outdebit

I am using mssql 8 express
0
Comment
Question by:goodk
  • 8
  • 7
  • 2
  • +2
20 Comments
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 35498582
SELECT Job,type,
(InCredit+OutDebit) AS SumOfAmount
FROM AceData
WHERE type IS NOT NULL

should work
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 35498585
sorry I misread it
0
 
LVL 9

Expert Comment

by:sshah254
ID: 35498616
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:goodk
ID: 35498727
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
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35498752

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
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35498755
Missed a bracket there


FOR type IN([fee],[tax],[nil])
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35498762

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
0
 

Author Comment

by:goodk
ID: 35498898
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


0
 
LVL 41

Expert Comment

by:ralmada
ID: 35499118
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)

Open in new window

0
 

Author Comment

by:goodk
ID: 35500760
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)
'.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35501511
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)

Open in new window

0
 

Author Comment

by:goodk
ID: 35710830
does not run - still see errors - I am using sql 8 express
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35711016
>> does not run - still see errors

What are the errors?
0
 

Author Comment

by:goodk
ID: 35715683

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)
'.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35722274
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)

Open in new window

0
 

Author Comment

by:goodk
ID: 35743791
may be the last one - thanks

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

Accepted Solution

by:
Sharath earned 1000 total points
ID: 35743826
corrected the error.
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)

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 35746032
there was a missing bracket in line 15. This should work:

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

Assisted Solution

by:ralmada
ralmada earned 1000 total points
ID: 35746040
here's the complete code
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)

Open in new window

0
 

Author Closing Comment

by:goodk
ID: 35754316
thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question