Formatting the T-SQL ResultSet

1
votes

H All,

I have following ResultSet generated by some 3-4 queries in T-Sql. The result looks something like this:

Month Payment
January 200
February 200
March 200
April 200
june 200
July 200
August 200
September 200
October 200
November 200
December 200

Now, depending on userdefined value, lets say 4, I need to arrange them some like this.

Month Payment
January;Febrary;March;April 200
May;June;july;August 200
............. so on.

If the user has chosen for 3, then,

Month Payment
January;Febrary;March 200
April;May;June 200
............. so on.

How do I do this? Any suggestions? Thanks in Advance!! :))

2
tsql
SQL Server handles the data - formatting like this should be handled by your front-end application or your report writer.... – marc_s
Hi Derek Kromm, I have accepted only those questions whose answers have given me some clue to sort my issues. I hope you also dont want me to up vote the answers which are of no use. I am using Sql SERVER 2008. – Raj Jayaswal
Hi marc_s, Don't mind but I dont agree to this. Sql Server is compatible enough to handle these small formatting issues. – Raj Jayaswal
will the payment always be the same ? – t-clausen.dk
@Raj: databases generally are not supposed to do formatting tasks - you say its a 'small' formatting task, but where is the line? To keep things simple, you should just do it in your presentation layer. – M.R.

2 Answers

2
votes

I honestly don't know how to handle payment since you have not described it, here is a way of solving it with recusive sql

declare @t table([Month] varchar(12), Payment int)
declare @uservalue int
set  @uservalue = 3
insert @t values('January',200)
insert @t values('February', 200)
insert @t values(' March', 200)
insert @t values('April', 200)
insert @t values('May', 200)
insert @t values('June', 200)
insert @t values('July', 200)
insert @t values('August', 200)
insert @t values('September', 200)
insert @t values('October', 200)
insert @t values('November', 200)
insert @t values('December', 200)

;with a as(
select month, payment, rn = row_number() over (order by (select 1))
from @t
), b as
(
select cast(month as varchar(200)) month, payment, rn
from a where (rn - 1) % @uservalue = 0
union all
select cast(b.month +';'+ a.month as varchar(200)), a.payment, a.rn
from b join a on a.rn - 1 = b.rn and b.rn %@uservalue > 0
)
select month, payment from b where rn % @uservalue = 0
0
votes

Here is an xml solution

declare @t table([Month] varchar(12), Payment int)
declare @uservalue int
set  @uservalue = 3
insert @t values('January',200)
insert @t values('February', 200)
insert @t values(' March', 200)
insert @t values('April', 200)
insert @t values('May', 200)
insert @t values('June', 200)
insert @t values('July', 200)
insert @t values('August', 200)
insert @t values('September', 200)
insert @t values('October', 200)
insert @t values('November', 200)
insert @t values('December', 200)

;WITH x AS ( select payment, rn = (row_number() over(order by (select 1)) - 1)/@uservalue, month
FROM @t
) 
SELECT REPLACE(STUFF(( 
SELECT ',' + [month] 
FROM x t 
WHERE t.rn = x.rn
for xml path(''), type 
).value('.', 'varchar(max)'), 1, 1, ''), ',', ' ') [month], payment FROM x
GROUP BY rn, payment