how to specify an exact amount of rows to display in a table?

Question

how to specify an exact amount of rows to display in a table?

anthony 80098

SSC Journeyman

Points: 79
More actions
November 9, 2011 at 6:19 am

#245912

I have a table which currently displays the top 15 records for each group in my dataset. How do i specify that the number of rows that make up the Details for each Group must always be 15 as i sometimes return less data? The details on the report are exported to pdf for printing so i need to ensure that i always display the same number of rows. Any help will be appreciated.
Anthony

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 1

How do i specify that the number of rows that make up the Details for each Group must always be 15 as i sometimes return less data? The details on the report are exported to pdf for printing so i need to ensure that i always display the same number of rows.

Are there always at least 15 detail rows for each group in the table? If not, what do you want to display for groups that only have, say, 1 or 2 detail rows?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

anthony 80098 SSC Journeyman Points: 79 More actions · Answer 2

Hi Jeff, even in the instance where there are only 2 rows, i still need to display 15 rows.

Ray M SSC-Insane Points: 21093 More actions · Answer 3

What your requesting is kind of unusual, and I can't think of an easy way to display 15 rows.

Would it be acceptable if you set the option on your group to page break after each group, or page break before each group, what ever you require.

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

anthony 80098 (11/9/2011)
Hi Jeff, even in the instance where there are only 2 rows, i still need to display 15 rows.

We got that you want to display 15 rows. What Jeff asked was, what do you want to display when there aren't 15 matching rows?

Here's my very quick knock up of what I think you're requesting.

--Standard TestEnvironment of 1,000,000 rows of random-ish data

IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment

END

--1,000,000 Random rows of data

SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,

RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME) AS randomDate,

ABS(CHECKSUM(NEWID())) AS randomBigInt,

(ABS(CHECKSUM(NEWID())) % 100) + 1 AS randomSmallInt,

RAND(CHECKSUM(NEWID())) * 100 AS randomSmallDec,

RAND(CHECKSUM(NEWID())) AS randomTinyDec,

RAND(CHECKSUM(NEWID())) * 100000 AS randomBigDec,

CONVERT(VARCHAR(6),CONVERT(MONEY,RAND(CHECKSUM(NEWID())) * 100),0) AS randomMoney,

CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65) +

CONVERT(CHAR(1),(ABS(CHECKSUM(NEWID())) % 9) + 1) + CONVERT(CHAR(1),(ABS(CHECKSUM(NEWID())) % 9) + 1)

+ ' ' + CONVERT(CHAR(1),(ABS(CHECKSUM(NEWID())) % 9) + 1) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65) +

CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65) AS randomPseduoPostCode

INTO #testEnvironment

FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3

--Query

SELECT ISNULL(CONVERT(VARCHAR(6),ID),'') AS ID,

ISNULL(CONVERT(VARCHAR(23),randomDate,21),'') AS randomDate,

ISNULL(CONVERT(VARCHAR(12),randomBigInt),'') AS randomBigInt,

ISNULL(CONVERT(VARCHAR(2),randomSmallInt),'') AS randomSmallInt,

ISNULL(CONVERT(VARCHAR(20),randomSmallDec),'') AS randomSmallDec,

ISNULL(CONVERT(VARCHAR(20),randomTinyDec),'') AS randomTinyDec,

ISNULL(CONVERT(VARCHAR(20),randomBigDec),'') AS randomBigDec,

ISNULL(CONVERT(VARCHAR(4),randomMoney),'') AS randomMoney,

ISNULL(CONVERT(VARCHAR(8),randomPseduoPostCode),'') AS randomPseduoPostCode

FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n, *

FROM (SELECT * FROM #testEnvironment

WHERE ID < 10) inside ) a

RIGHT OUTER JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION

SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION

SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION

SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION

SELECT 13 UNION SELECT 14 UNION SELECT 15) b(x) ON a.n = b.x

ORDER BY ISNULL(ID, (SELECT MAX(ID)+1 FROM #testEnvironment))

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

anthony 80098 SSC Journeyman Points: 79 More actions · Answer 5

Hi Ray,

I know this request is quite strange but i require the data to be displayed on 1 page. I have 2 tables both showing similar data which needs to be displayed next to each other. The one table displays 15 records per group but the second table has less records in some instances. Because the headers are being repeated for every group, it looks weird when table 1 has 15 lines and the accompanying data in table 2 has less lines as the headers now don't align. I've attached a screenshot as to what the result is when the 2 tables don't have equal rows to display. This one really is strange.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 6

anthony 80098 (11/9/2011)
Hi Ray,
I know this request is quite strange but i require the data to be displayed on 1 page. I have 2 tables both showing similar data which needs to be displayed next to each other. The one table displays 15 records per group but the second table has less records in some instances. Because the headers are being repeated for every group, it looks weird when table 1 has 15 lines and the accompanying data in table 2 has less lines as the headers now don't align. I've attached a screenshot as to what the result is when the 2 tables don't have equal rows to display. This one really is strange.

So using your example, WHAT is it you want displayed in the currently missing 15th row of your example?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286609 More actions · Answer 7

It seems that the OP wants to keep the same space as 15 rows so the pdf doesnt get jumbled up. Maybe you can do a union to 15 empty rows and select the top 15?

Kind of kludgy but it would work.

create table #test

(

col1 varchar(10),

col2 varchar(10),

col3 varchar(10),

col4 varchar(10)

)

go

insert #Test

select '', '', '', ''

go 15

select top 15 Column1, Column2, Column3, Column4 from

(

select 'Val1' as Column1, 'Val2' as Column2, 'Val3' as Column3, 'Val4' as Column4, 0 as SortOrder --This would be your real table that returns 1 row

union all

select col1, col2, col3, col4, 1 as SortOrder from #test

) x order by SortOrder

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 8

Yep... what I'm trying to figure out is does the OP want a blank row or a row with the number 15 in it or what?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

anthony 80098 SSC Journeyman Points: 79 More actions · Answer 9

anthony 80098

SSC Journeyman

Points: 79

November 10, 2011 at 12:16 am

#1405542

A blank row is all that is required at this point.

anthony 80098 SSC Journeyman Points: 79 More actions · Answer 10

Hi all,

I've managed to figure this one out by simply adding 15 footer records to my group and then hiding these when i have records to display. This then displays a blank line until i have 15 rows in my table:w00t:

Cadavre SSC-Forever Points: 41690 More actions · Answer 11

anthony 80098 (11/10/2011)
Hi all,
I've managed to figure this one out by simply adding 15 footer records to my group and then hiding these when i have records to display. This then displays a blank line until i have 15 rows in my table:w00t:

Glad you worked out your own way. What was wrong with the ways that were shown by myself and Sean?

Here is how we showed you: -