February 25, 2008 at 9:48 pm
Hi. I have a query in an .asp page that returns the two columns of data correctly, but I would like the data split into three sets of the two columns so that it shows on one page. For example rows 1-15 for column1 and column2 would show on the far left, then in the middle of the page would be rows 16 - 30 of column1 and column2, and then on the right would be rows 31 - 45 of column1 and column2. Right now it shows my data in the two columns with all 45 rows going down the page. Is there any way to change the query to display the results in multiple columns?
February 25, 2008 at 9:58 pm
Are there ways, yes, but typically display is handled in the front end. There is no row number function in SQL 7, 2000 so to handle your request you would need to stage the data in a temp table/table variable with a identity field and then query it like this:
Create table #values
(
id int identity(1,1),
value varchar(25)
)
Declare @rows Int
Declare @Start Int
Declare @End Int
Insert into #values
(
value
)
Select
value
from
table
Where
column = criteria
Set @rows = @@RowCount
Set @Start = 1
Set @End = @Rows/3
Select
value,
(Select value from #values Where id between @Start + @End and @End + @End) as value2,
(Select value from #values Where id between @Start + @End * 2 and @End + @End * 2) as value3
From
#values
Where
id between @Start and @End
Or
You could do it in the asp code just where is belongs.
For i = 1 to recordset.count
Select Case i
Case <=15
column1 in table
Case between 16 and 30
column2 in table
CAse between 31 and 45
column3 in table
Else
handle more than 45 rows
End Case
EndFor
My asp is a bit rusty but you probably get the idea.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 26, 2008 at 12:15 am
I've never had the luxury (or maybe its "PITA") of working with something like Crystal reports or ASP or any of that nice GUI reporting stuff... I agree, it should probably be done in the GUI, especially for "News Paper Columns" like what you're asking for.
That, not withstanding, here's how I normally end up doing it just because I don't have the GUI or the report formatter available...
--===== Identify the Database to use
USE Northwind
--===== Setup for a "pretty" display
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
--===== Transfer data to a "formatting" table to get row numbers in SQL Server 2000
SELECT IDENTITY(INT,0,1) AS ItemNum,
CAST(ProductID AS INT) AS ProductID, --Cast necessary to strip identity property
ProductName
INTO #Format
FROM dbo.Products
ORDER BY ProductName
--===== Return the items in a 2x3 page array as requested
SELECT
MAX(CASE WHEN Col = 1 THEN STR(ProductID) ELSE '' END) AS ProductID,
MAX(CASE WHEN Col = 1 THEN ProductName ELSE '' END) AS ProductName,
MAX(CASE WHEN Col = 2 THEN STR(ProductID) ELSE '' END) AS ProductID,
MAX(CASE WHEN Col = 2 THEN ProductName ELSE '' END) AS ProductName,
MAX(CASE WHEN Col = 3 THEN STR(ProductID) ELSE '' END) AS ProductID,
MAX(CASE WHEN Col = 3 THEN ProductName ELSE '' END) AS ProductName,
MAX(CASE WHEN Row = 15 THEN CHAR(13)+CHAR(13) ELSE '' END)
FROM (
SELECT ProductID,
ProductName,
((ItemNum)/45)+1 AS Page,
((ItemNum)/15)%3+1 AS Col,
((ItemNum)%15)+1 AS Row
FROM dbo.#Format
) d
GROUP BY Page,Row
ORDER BY Page,Row
DROP TABLE #Format
--===== If you want to "balance" the last page, heh... good luck.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply