Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating comma separated values for a column in TSQL Expand / Collapse
Author
Message
Posted Monday, July 29, 2013 12:14 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 6:47 AM
Points: 63, Visits: 560
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
Post #1478695
Posted Monday, July 29, 2013 1:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 6:47 AM
Points: 63, Visits: 560
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
Post #1478720
Posted Wednesday, July 31, 2013 3:17 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:58 AM
Points: 2,397, Visits: 3,407
-- 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"
Post #1479715
Posted Tuesday, August 6, 2013 8:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 7, 2013 4:04 PM
Points: 42, Visits: 107
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
Post #1481631
Posted Tuesday, August 6, 2013 11:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 37,075, Visits: 31,636
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1481647
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse