July 29, 2013 at 12:14 pm
Hi All,
Need help in writing tsql for creating comma separated values for a table based on batch size.
For eg i have table tblA with 10 records with following records
ID
1
2
3
..
10
Now i want to create a column that will have comma separated values with 2 values each if i pass the batch size as '2' i.e
1,2
3,4
..
9, 10
similarly if i pass the batch size as 5 then there will be 2 set of records like,
1,2,3,4,5
6,7,8,9,10
Please let me know how to implement the logic.
Thanks in advance.
Sam
July 29, 2013 at 1:10 pm
I did using the following code .using northwind database..
with mycte as(select ntile(15) over( order by customerid) as grp1, customerid from dbo.Customers)
,mycte2 as(select distinct grp1 from mycte)
SELECT grp1
, customerid = STUFF((
SELECT ',' + customerid
FROM mycte
WHERE grp1 = mycte2.grp1
ORDER BY customerid
FOR XML PATH(''), TYPE).value('.','varchar(100)'), 1, 1, '')
FROM mycte2
with ntile im breaking the records as per the required group..
thanks
July 31, 2013 at 3:17 pm
-- Prepare sample data
DECLARE@Sample TABLE
(
Value INT NOT NULL
);
INSERT@Sample
(
Value
)
SELECTNumber
FROMmaster.dbo.spt_values
WHERE[Type] = 'P';
-- SwePeso
DECLARE@BatchSize TINYINT = 5;
WITH cteSource(Value, theGrp)
AS (
SELECTValue,
(ROW_NUMBER() OVER (ORDER BY Value) - 1) / @BatchSize AS theGrp
FROM@Sample
)
SELECTd.theGrp,
STUFF(f.Data, 1, 1, '') AS Data
FROM(
SELECTNumber AS theGrp
FROMmaster.dbo.spt_values
WHERE[Type] = 'P'
AND Number <= (SELECT COUNT(*) FROM @Sample) / @BatchSize
) AS d
CROSS APPLY(
SELECT',' + CAST(x.Value AS VARCHAR(12))
FROMcteSource AS x
WHEREx.theGrp = d.theGrp
ORDER BYx.Value
FOR XMLPATH('')
) AS f(Data);
N 56°04'39.16"
E 12°55'05.25"
August 6, 2013 at 8:49 pm
How about this?
To change the number of batch, change the value of @batch variable.
declare @batch as smallint
set @batch = 6
if object_id('tempdb..#sample') is not null
drop table #sample
SELECT top 20
abs(id) as number
into #sample
FROMmaster.dbo.sysobjects
select * from #sample
;with batched
as
(
select
batch_id = (row_number() over(order by number) - 1)/@batch,
number
from #sample
), sorted
as
(
select
batch_id,
sort_id = row_number() over(partition by batch_id order by number),
number
from batched
),GatherAll
as
(
select batch_id, sort_id, cast(number as varchar(max)) as Gathered
from sorted
where sort_id = 1
union all
select s.batch_id, s.sort_id, cast(rtrim(Gathered) + ',' + cast(s.number as varchar(50)) as varchar(max))
from sorted s
inner join GatherAll g on g.batch_id = s.batch_id and s.sort_id = g.sort_id + 1
)
select batch_id, max(Gathered)
from GatherAll
group by Batch_Id
Happy Coding!!!
~~ CK
August 6, 2013 at 11:05 pm
ck9663 (8/6/2013)
How about this?To change the number of batch, change the value of @batch variable.
declare @batch as smallint
set @batch = 6
if object_id('tempdb..#sample') is not null
drop table #sample
SELECT top 20
abs(id) as number
into #sample
FROMmaster.dbo.sysobjects
select * from #sample
;with batched
as
(
select
batch_id = (row_number() over(order by number) - 1)/@batch,
number
from #sample
), sorted
as
(
select
batch_id,
sort_id = row_number() over(partition by batch_id order by number),
number
from batched
),GatherAll
as
(
select batch_id, sort_id, cast(number as varchar(max)) as Gathered
from sorted
where sort_id = 1
union all
select s.batch_id, s.sort_id, cast(rtrim(Gathered) + ',' + cast(s.number as varchar(50)) as varchar(max))
from sorted s
inner join GatherAll g on g.batch_id = s.batch_id and s.sort_id = g.sort_id + 1
)
select batch_id, max(Gathered)
from GatherAll
group by Batch_Id
Happy Coding!!!
~~ CK
Please see the following article for why you might not want to use a recursive CTE to count.
[font="Arial Black"]Hidden RBAR: Counting with Recursive CTE's[/font]
[/url]
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply