December 26, 2018 at 2:28 am
Hi All,
My table have a 10000 records and my table like
Id Name
1 abcd
1 efgh
1 xyz
.........
.........
up to 10000records
Finally how to select each row 500 records(with comma separate values string) from table
My results like
Id Name
1 abcd,efgh,xyz(500 records)
1 qwe,rty,uio(500 records)
1 asd,fgh,jkl(500 records)
up to end
Please help me on this.
Regards
Pols
December 26, 2018 at 6:44 am
Not sure why you would need this, but here is an example of how to put 3 records into comma separated values.
drop table if exists #t
go
create table #t
(uniqueid int identity(1,1),
Id int,
Name varchar(10))
insert into #t values
(1,'abcd'),
(1,'efgh'),
(1,'xyz'),
(1,'qwe'),
(1,'rty'),
(1,'uio'),
(1,'asd'),
(1,'fgh'),
(1,'jkl'),
(2,'asd'),
(2,'fgh'),
(2,'jkl')
insert into #t values
(3,'abcd')
;with cteRow as (
select ID, Name,
Row_number() over (partition by ID order by UniqueID) RowNum
from #t),
cteGroup as (
select id, count(1) N
from #t
group by ID),
cte as(
select c.ID, c.Name, c.RowNum, RowNum % 3 as RowGroup
from cteRow c
join CteGroup g
on c.ID = g.ID
)
select c.ID, c.RowGroup,
STUFF(
( SELECT ',' + t2.Name
FROM cte t2
WHERE c.ID = t2.ID
and c.RowGroup = t2.RowGroup
FOR XML PATH('')
),
1,
1,''
) AS t
from cte c
group by c.ID, c.RowGroup
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 26, 2018 at 7:06 am
Thanks for reply Mike01,
Above results is
ID RowGroup t
1 0 xyz,uio,jkl
1 1 abcd,qwe,asd
1 2 efgh,rty,fgh
2 0 jkl
2 1 asd
2 2 fgh
3 1 abcd
But i want each row 500 records for same group
Like above results lik
ID RowGroup t
1 0 xyz,uio,jkl, abcd,qwe,asd, efgh,rty,fgh (500 records)
2 0 jkl,asd, fgh(500 records)
3 0 abcd(500 records)
Regards
Pols
December 26, 2018 at 7:22 am
It was just an example. I used this to get the groupings.
RowNum % 3
You can modify to this: RowNum % 500, I just wasn't going to create all of your data
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 26, 2018 at 7:41 am
Thank you...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply