 |
 |
|
|
|
|
 |
|
|
General
The ever-powerful List X module forum. Post your questions, new configuration ideas and reviews.
| You are not authorized to post a reply.
|
|
| Author |
Messages |
|
mlarsen
Posts:49
 |
| 12/22/2006 10:32 AM |
 Alert
|
| I am trying to create a Multi-Column Layout that displays down then across. I have a header group display then items and after I get half way through the records I would like to start a new column. Any way to do accomplish this? |
|
|
|
|
bgates
Posts:197
|
| 12/26/2006 10:04 PM |
Alert
|
That would be quite complicated. I know you can do a "SELECT TOP 50 PERCENT ... " for your main query, and that will return the first half of the records. The second half would have to be done using an inner SELECT statement that selects the TOP 50 PERCENT, but the order is reverse (essentially, grab the last 50 percent). The outer SELECT statement would then reverse the order so that it comes out the same. End result: two separate result sets each containing approximately half of the records. So then the problem becomes: how do I get it into a single resultset so that ListX can handle it nicely.
Here are the steps you'll have to do in your main query:
1) Create a temporary table with an identity column (say, RowNumber) and has 2 columns for every column you want displayed. For example, if you're doing a side-by-side listing of the Tabs in your system, then create a temp table with fields: TabID1, TabName1, TabID2, TabName2.
2) Insert into the fields ending with 1 using the statement "SELECT TOP 50 PERCENT ...".
3) Insert into the fields ending with 2 using the reversal statement "SELECT * FROM (SELECT TOP 50 PERCENT FROM... ORDER BY ...)"
4) Update the fields in the first half of the records with data from the second half.
5) Delete the second half of the records.
In your formatting, use the fields TabID1 and TabID2 to show the tab ids side-by-side. The complete SQL is below.
DECLARE @HalfCount int /* This is used for marking the first half of the records */
CREATE TABLE #tmpTabs
(
RowNumber [int] IDENTITY(1,1) NOT NULL,
TabID1 [int],
TabName1 [nvarchar](250),
TabOrder1 [int],
TabID2 [int],
TabName2 [nvarchar](250),
TabOrder2 [int]
)
INSERT INTO #tmpTabs (TabID1, TabName1, TabOrder1)
SELECT TOP 50 PERCENT TabID, TabName, TabOrder
FROM Tabs
ORDER BY TabOrder ASC
SELECT @HalfCount = SCOPE_IDENTITY() /* Retains RowNumber for last record written */
INSERT INTO #tmpTabs (TabID2, TabName2, TabOrder2)
SELECT *
FROM ( /* This is the inner reversed statement */
SELECT TOP 50 PERCENT TabID, TabName, TabOrder
FROM Tabs
ORDER BY TabOrder DESC) ReverseTabs
ORDER BY TabOrder ASC
/* Update the first half of the records with data from the second half */
UPDATE #tmpTabs SET
TabID2 = Half.TabID2,
TabName2 = Half.TabName2,
TabOrder2 = Half.TabOrder2
FROM #tmpTabs INNER JOIN (SELECT (RowNumber - @HalfCount) AS RowNumber, TabID2, TabName2, TabOrder2 FROM #tmpTabs) Half
ON #tmpTabs.RowNumber = Half.RowNumber
/* Remove the extraneous half */
DELETE FROM #tmpTabs WHERE RowNumber > @HalfCount
SELECT * FROM #tmpTabs ORDER BY TabOrder1, TabOrder2
Your format is then simply:
<tr><td>[TabName1]</td><td> | </td><td>[TabName2]</td></tr> |
|
Bob Gates
Business Intelligence Force, Inc. (bi4ce) |
|
|
mlarsen
Posts:49
|
| 12/27/2006 9:58 AM |
Alert
|
| That worked perfectly! Thanks Bob! |
|
|
|
|
mstoyanovich
Posts:3
|
| 10/05/2008 3:45 PM |
Alert
|
I'm trying to do the same with ListX as you noted a couple of years ago (slightly different with the following code - I added comments per your original post so you can see what I'm doing - and am getting an error).
First, the SQL...
DECLARE @HalfCount int /* As noted this is used for marking the first half of the records */
CREATE TABLE #tmpSeries
(
RowNumber [int] IDENTITY(1,1) NOT NULL,
SeriesID1 [int],
SeriesDescription1 [nvarchar](250),
SeriesID2 [int],
SeriesDescription2 [nvarchar](250),
)
INSERT INTO #tmpSeries (SeriesID1, SeriesDescription1)
SELECT TOP 50 PERCENT SeriesID, SeriesDescription
FROM Series
WHERE dbo.Series.SeriesID NOT IN (3, 20, 18, 7, 13, 26, 23)
ORDER BY SeriesDescription ASC
SELECT @HalfCount = SCOPE_IDENTITY() /* Retain RowNumber for last record written */
INSERT INTO #tmpSeries (SeriesID2, SeriesDescription2)
SELECT *
FROM ( /* As noted, here is the inner reversed statement */
SELECT TOP 50 PERCENT SeriesID, SeriesDescription
FROM Series
WHERE dbo.Series.SeriesID NOT IN (3, 20, 18, 7, 13, 26, 23)
ORDER BY SeriesDescription DESC) ReverseSeries
ORDER BY SeriesDescription ASC
/* Update the first half of the records with data from the second half */
UPDATE #tmpSeries SET
SeriesID2 = Half.SeriesID2,
SeriesDescription2 = Half.SeriesDescription2,
FROM #tmpSeries INNER JOIN (SELECT (RowNumber - @HalfCount) AS RowNumber, SeriesID2, SeriesDescription2 FROM #tmpSeries) Half
ON #tmpSeries.RowNumber = Half.RowNumber
/* Remove the extraneous half */
DELETE FROM #tmpSeries WHERE RowNumber > @HalfCount
SELECT * FROM #tmpSeries ORDER BY SeriesDescription1, SeriesDescription2
Now the error...
I keep getting an error in the UPDATE statement, specifically in the From record...
Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'Half'.
Anything I'm missing? I've been working on a lot of other things and don't doubt I'm missing something obvious.
I'm using SQL Server 2005...
Thanks in advance. |
|
|
|
|
|
| You are not authorized to post a reply. |
|
|
|
ActiveForums 3.6
|
|
| Latest Post
|
 |
|
At R2integrated (formerly Bi4ce), we take support seriously. That's why we support our customers and DNN community with daily monitoring from our experienced engineering team. We ask that the first step taken is to read the relevant documentation and support forums prior to submitting any questions that may already be available or have been answered. We ask that you review the documentation that we provide for our products before posting a question.
The Forums are for our customers to chat, exchange ideas and strategies, and submit feedback. Please be sure to perform keyword searches for previous related forum responses.
To be helpful when submitting a new item, please include the following:
- DNN Version
- Module Version
- Admin Log Viewer Information
- Environment detail: Operating system, .NET framework version, database and version, IIS version, Browser version (if appropriate)
We always try to respond quickly and monitor the forums daily during business hours (EST). Occasionally, requests for a specific project requirement may not apply for the free support offered. For project specific support please submit via our Information Request form.
Thank you for using our Forums.
Click here to register for the Forums
|
|
|
|
| © 2008 by R2integrated (formerly Bi4ce) | DNN® is a registered trademark of DotNetNuke Corporation
|
 |
|
|
|
 |
|