Formatting the T-SQL ResultSet

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!! :))

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

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