SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating comma separated values for a column in TSQL


Creating comma separated values for a column in TSQL

Author
Message
sam 55243
sam 55243
Say Hey Kid
Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)

Group: General Forum Members
Points: 680 Visits: 953
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
sam 55243
sam 55243
Say Hey Kid
Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)

Group: General Forum Members
Points: 680 Visits: 953
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
SwePeso
SwePeso
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9659 Visits: 3433
-- Prepare sample data
DECLARE @Sample TABLE
(
Value INT NOT NULL
);

INSERT @Sample
(
Value
)
SELECT Number
FROM master.dbo.spt_values
WHERE [Type] = 'P';

-- SwePeso
DECLARE @BatchSize TINYINT = 5;

WITH cteSource(Value, theGrp)
AS (
SELECT Value,
(ROW_NUMBER() OVER (ORDER BY Value) - 1) / @BatchSize AS theGrp
FROM @Sample
)
SELECT d.theGrp,
STUFF(f.Data, 1, 1, '') AS Data
FROM (
SELECT Number AS theGrp
FROM master.dbo.spt_values
WHERE [Type] = 'P'
AND Number <= (SELECT COUNT(*) FROM @Sample) / @BatchSize
) AS d
CROSS APPLY (
SELECT ',' + CAST(x.Value AS VARCHAR(12))
FROM cteSource AS x
WHERE x.theGrp = d.theGrp
ORDER BY x.Value
FOR XML PATH('')
) AS f(Data);




N 56°04'39.16"
E 12°55'05.25"
ck9663
ck9663
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 111
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
FROM master.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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215725 Visits: 41981
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
FROM master.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.
Hidden RBAR: Counting with Recursive CTE's


--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search