February 15, 2016 at 12:50 am
Hi to all,
I have got a one column in table containing different Client ID's. There are 3000 rows in a table ...I need a data with comma separated(each row should not be more than 999 Client ID's)
EX
ROW1: 1,2----999
ROW2: 1000,1001,.....1998
ROW3: 1999,2000-----2997
ROW4-2998,2999,3000
So in this case there are 4 rows (output), if you have any questions please do let me know
Thanks,
Dan.
February 15, 2016 at 1:25 am
koti.raavi (2/15/2016)
Hi to all,I have got a one column in table containing different Client ID's. There are 3000 rows in a table ...I need a data with comma separated(each row should not be more than 999 Client ID's)
EX
ROW1: 1,2----999
ROW2: 1000,1001,.....1998
ROW3: 1999,2000-----2997
ROW4-2998,2999,3000
So in this case there are 4 rows (output), if you have any questions please do let me know
Thanks,
Dan.
Quick example
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_SIZE INT = 3050;
DECLARE @ROW_SIZE INT = 1000
IF OBJECT_ID(N'dbo.TBL_SAMPLE_CLIENT') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_CLIENT;
CREATE TABLE dbo.TBL_SAMPLE_CLIENT
(
CUST_ID INT NOT NULL CONSTRAINT PK_DBO_TBL_SAMPLE_CLIENT_CUST_ID PRIMARY KEY CLUSTERED
);
INSERT INTO dbo.TBL_SAMPLE_CLIENT (CUST_ID)
SELECT
TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns SAC01
CROSS JOIN sys.all_columns SAC02
CROSS JOIN sys.all_columns SAC03;
;WITH BASE_DATA AS
(
SELECT
SC.CUST_ID
,FLOOR(ROW_NUMBER() OVER
(
ORDER BY SC.CUST_ID
) / @ROW_SIZE) AS GRP_ID
FROM dbo.TBL_SAMPLE_CLIENT SC
)
,GROUP_LIST AS
(
SELECT
DISTINCT BD.GRP_ID
FROM BASE_DATA BD
)
SELECT
GL.GRP_ID
,STUFF(
( SELECT
CHAR(44) + CONVERT(VARCHAR(12),BD.CUST_ID,0)
FROM BASE_DATA BD
WHERE GL.GRP_ID = BD.GRP_ID
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(8000)'),1,1,'') AS ID_LIST
FROM GROUP_LIST GL
ORDER BY GL.GRP_ID;
Partial output
GRP_ID ID_LIST
-------- ------------------------------------------------//--------------/
0 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,\\,85,86,87,88,81 1000,1001,1002,1003,1004,1005,1006,1007,1008,100//48,1049,1050,1/
2 2000,2001,2002,2003,2004,2005,2006,2007,2008,200\\48,2049,2050,23 3000,3001,3002,3003,3004,3005,3006,3007,3008,300//48,3049,3050 /
February 15, 2016 at 2:22 am
Thanks for quick response...Working fine ..Thank you again 🙂
February 15, 2016 at 8:36 am
Eirikur,
Any reason for using FLOOR on an integer division?
February 15, 2016 at 9:44 am
Luis Cazares (2/15/2016)
Eirikur,Any reason for using FLOOR on an integer division?
Old oracle habit
😎
February 15, 2016 at 10:24 am
Eirikur Eiriksson (2/15/2016)
Luis Cazares (2/15/2016)
Eirikur,Any reason for using FLOOR on an integer division?
Old oracle habit
😎
:sick:Oracle:sick:
February 15, 2016 at 1:27 pm
Luis Cazares (2/15/2016)
Eirikur Eiriksson (2/15/2016)
Luis Cazares (2/15/2016)
Eirikur,Any reason for using FLOOR on an integer division?
Old oracle habit
😎
:sick:Oracle:sick:
yup, the difference between \ and / :pinch:
😎
February 15, 2016 at 1:49 pm
Eirikur Eiriksson (2/15/2016)
Luis Cazares (2/15/2016)
Eirikur Eiriksson (2/15/2016)
Luis Cazares (2/15/2016)
Eirikur,Any reason for using FLOOR on an integer division?
Old oracle habit
😎
:sick:Oracle:sick:
yup, the difference between \ and / :pinch:
😎
We got you terminating your SQL statements with semicolons, now if we could get you to stop starting CTEs with semicolons. ;-):w00t:
February 15, 2016 at 2:20 pm
Lynn Pettis (2/15/2016)
Eirikur Eiriksson (2/15/2016)
Luis Cazares (2/15/2016)
Eirikur Eiriksson (2/15/2016)
Luis Cazares (2/15/2016)
Eirikur,Any reason for using FLOOR on an integer division?
Old oracle habit
😎
:sick:Oracle:sick:
yup, the difference between \ and / :pinch:
😎
We got you terminating your SQL statements with semicolons, now if we could get you to stop starting CTEs with semicolons. ;-):w00t:
He he, it's a constant circle, recursive, iterative, repetition, how does on tell he beginninators from the terminators?
😎
BTW, can you find me one peace of code I've posted which isn't terminated by a "beginninator"?
February 16, 2016 at 9:21 am
Here is a different approach using a zero-based tally table. I've used a CTE to create a tally table, but you can skip that if you already have a tally table.
DECLARE @grp_size INT = 50;
WITH Tally(n) AS (
SELECT ROW_NUMBER() OVER(ORDER BY n) - 1
FROM (
VALUES(1), (1), (1), (1), (1), (1)
) AS v(n)
)
SELECT n + 1 AS group_num,
STUFF(
(SELECT ', ', ROW_NUMBER() OVER( ORDER BY c.object_id) AS [*]
FROM sys.columns c
ORDER BY [*]
OFFSET n * @grp_size ROWS
FETCH NEXT @grp_size ROWS ONLY
FOR XML PATH(''), TYPE
).value('(./text())[1]','VARCHAR(8000)'), 1, 1, ''
)
FROM Tally
This uses the OFFSET/FETCH that was introduced in SQL 2012.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 16, 2016 at 1:25 pm
I did a quick comparison of Eirikur's and my methods. Here are the results:
1) Eirikur's method shorts the first row, because he starts with 1 rather than 0.
2) Eirikur's method requires an extra table scan (presumably to retrieve the group id).
3) My method requires a TOP clause or it will return rows with NULL values (and take much longer to run).
Eirikur's method on 30,050 rows
SQL Server parse and compile time:
CPU time = 19 ms, elapsed time = 19 ms.
Table '#TBL_SAMPLE_CLIENT__________________________________________________________________________________________________000000151128'. Scan count 32, logical reads 1632, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 406 ms, elapsed time = 414 ms.
My method on 30,050 rows
SQL Server parse and compile time:
CPU time = 15 ms, elapsed time = 18 ms.
Table 'Worktable'. Scan count 62, logical reads 418, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TBL_SAMPLE_CLIENT__________________________________________________________________________________________________000000151128'. Scan count 31, logical reads 873, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syssingleobjrefs'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysidxstats'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 35, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 188 ms, elapsed time = 184 ms.
I used sys.columns to create my tally table, so there could be further improvements to mine by using another method to create the tally table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply