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.
Author |
Topic |
stoolpidgeon
Starting Member
28 Posts |
Posted - 2013-07-25 : 11:44:56
|
The question may seem a bit vague but the following should help to demonstrate my problem:declare @maxMonth integerdeclare @month integerset @month = 1set @maxMonth = (select max(month(importdate)) from tblsales)select while @month < @maxMonth begin (case month(importdate) when @month then invoicedvalue end) as (select cast(importdate as char(3)) where month(importdate) = @month), @month = @month + 1 endfrom tblsaleswhere importdate > '01/01/2013'Can't see why there would be an issue with this script except that if I loop on the case statement I end up with a final comma which I obviously don't want!Is there some way around this? |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-25 : 11:55:06
|
quote: Originally posted by stoolpidgeon The question may seem a bit vague but the following should help to demonstrate my problem:declare @maxMonth integerdeclare @month integerset @month = 1set @maxMonth = (select max(month(importdate)) from tblsales)select while @month < @maxMonth begin (case month(importdate) when @month then invoicedvalue end) as (select cast(importdate as char(3)) where month(importdate) = @month), @month = @month + 1 endfrom tblsaleswhere importdate > '01/01/2013'Can't see why there would be an issue with this script except that if I loop on the case statement I end up with a final comma which I obviously don't want!Is there some way around this?
Are you using Microsoft SQL Server? The syntax (even pseudo syntax) doesn't seem like it is T-SQL compliant.Regardless, one way to remove trailing comma would be to use a left function on your final output:LEFT(YourFinalOutput,LEN(YourFinalOutput)-1) If you are trying to concatenate values in a column of a table, there are faster and better approaches - here are couple of examples:http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/http://myshallowsqlblog.wordpress.com/the-lowdown-on-that-xml-path-thingie/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|