SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Does SQL have a way of exluding a final comma?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stoolpidgeon
Starting Member

United Kingdom
28 Posts

Posted - 07/25/2013 :  11:44:56  Show Profile  Reply with Quote
The question may seem a bit vague but the following should help to demonstrate my problem:

declare @maxMonth integer
declare @month integer

set @month = 1
set @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
end
from tblsales
where 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
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 07/25/2013 :  11:55:06  Show Profile  Reply with Quote
quote:
Originally posted by stoolpidgeon

The question may seem a bit vague but the following should help to demonstrate my problem:

declare @maxMonth integer
declare @month integer

set @month = 1
set @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
end
from tblsales
where 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/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/26/2013 :  00:11:54  Show Profile  Reply with Quote
The code looks like you attempt is to do dynamic pivoting based on monthnames to me


..
(case month(importdate) when @month then invoicedvalue end) as (select cast(importdate as char(3)) where month(importdate) = @month),
..


If yes, you dont need a loop
have a look at this approach

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000