Splitting a Table to Fit on an Excel Worksheet

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Splitting a Table to Fit on an Excel Worksheet

Author

Topic

singularity
Posting Yak Master

153 Posts

Posted - 2010-05-18 : 19:58:28

I have a table with ~280,000 rows that looks like this:


RN     CUSTOMER     MEMBER
1      ABC          123
2      ABC          124
3      DEF          234
4      DEF          235

I need to export the data out of this table into Excel. Since we're using an older version, I can only fit 65,535 rows per worksheet, so I will be splitting this data across 5 worksheets. However, it's not as simple as doing:


select * from mytable where rn between 1 and 65535;
select * from mytable where rn between 65535 and 131070;

etc.

I've been asked not to have any one customer spread across multiple tabs.

So if the data looks like this:


RN     CUSTOMER     MEMBER
65532     PQR         789
65533     PQR         790
65534     STU         800
65535     STU         801
65536     STU         802

I'd want RN's 1 through 65533 to be on the first tab because I can't split up customer "STU" across two tabs.

Anyone know of a good simple way to accomplish this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-19 : 01:49:32

only this method came to my mind right now . . .


-- Sample table
DECLARE @sample TABLE
(	
	RN		int identity,
	CUSTOMER	varchar(3),
	MEMBER		int
)

-- Sample Data
INSERT INTO @sample (CUSTOMER, MEMBER)
SELECT	'A',   123 UNION ALL
SELECT	'B',   124 UNION ALL
SELECT	'C',   234 UNION ALL
SELECT	'C',   235 UNION ALL
SELECT	'C',   124 UNION ALL
SELECT	'D',   234 UNION ALL
SELECT	'D',   234 UNION ALL
SELECT	'E',   235 UNION ALL
SELECT	'F',   124 UNION ALL
SELECT	'F',   234 UNION ALL
SELECT  'G',   235 UNION ALL
SELECT  'G',   124 UNION ALL
SELECT  'G',   234 UNION ALL
SELECT  'H',   235

-- Query
DECLARE	@pagesize	int

SELECT	@pagesize	= 4

; WITH
cte
AS
(
	SELECT	RN, CUSTOMER, MEMBER, CUSTCNT = row_number() OVER (PARTITION BY CUSTOMER ORDER BY RN DESC)
	FROM	@sample
),
rcte
AS
(
	SELECT	RN, CUSTOMER, MEMBER, CUSTCNT, LINES = 1, PAGE = 1
	FROM	cte
	WHERE	RN	= 1
	
	UNION ALL

	SELECT	c.RN, c.CUSTOMER, c.MEMBER, c.CUSTCNT, 
		LINES 	= CASE 	WHEN r.LINES + c.CUSTCNT <= @pagesize
				THEN r.LINES + 1
				ELSE 1
				END, 
		PAGE 	= CASE 	WHEN r.LINES + c.CUSTCNT <= @pagesize
				THEN r.PAGE
				ELSE r.PAGE + 1
				END
	FROM	cte c
		INNER JOIN rcte r	ON	c.RN	= r.RN + 1
)
SELECT	*
FROM	rcte

/*
RN          CUSTOMER MEMBER      CUSTCNT              LINES       PAGE        
----------- -------- ----------- -------------------- ----------- ----------- 
1           A        123         1                    1           1
2           B        124         1                    2           1
3           C        234         3                    1           2
4           C        235         2                    2           2
5           C        124         1                    3           2
6           D        234         2                    1           3
7           D        234         1                    2           3
8           E        235         1                    3           3
9           F        124         2                    1           4
10          F        234         1                    2           4
11          G        235         3                    1           5
12          G        124         2                    2           5
13          G        234         1                    3           5
14          H        235         1                    4           5

(14 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources