Newspaper columns in with T-SQL

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 2000 Forums

Transact-SQL (2000)

Newspaper columns in with T-SQL

Author

Topic

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2004-01-08 : 10:19:16

Hey all-
The other day I was listening to the demo\seminar Microsoft did on the new reporting services and someone asked a question about doing columns like what’s in a newspaper. The tech guru they had suggested that would be handled in a query design and not in a report. Any T-SQL guru out there know what he is talking about? I now have a reason to doing something like this and not really sure how to approach.

Reporting services aside, could this be done in T-SQL? I guess one could do a loop for a set amount iterations and then create a new column in a temp table and spit that out...ahh, thats crazy. Ideas?

Thanks-
Doug

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-08 : 13:24:34

Very interesting.. though have no any ideas so far..

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2004-01-08 : 15:05:26

I'd lean toward doing this on the client side. A report layout really, really sounds like something that should be done in the presentation layer and not in the database.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-01-08 : 17:43:58

I'd be more inclined to do this in the presentation layer, but what the hell...


Create Table #newsheadlines
(
	id int identity,
	headline varchar(100),
	brief varchar(300)
)

INSERT INTO #newsheadlines (headline, brief)
VALUES ('Mars Landing A Fake', 'Yep, you heard it here first')

INSERT INTO #newsheadlines (headline, brief)
VALUES ('Crikey, that was dumb', 'Crocodile hunter did something stupid')

INSERT INTO #newsheadlines (headline, brief)
VALUES ('Tara Hits New Post Record', 'Forces upgrade of post count field to bigint')

INSERT INTO #newsheadlines (headline, brief)
VALUES ('Larry Ellison in animal sex romp', 'High profile CEO in compromising position with sheep')

That sets up a test data table, I'd be inclined to create a temp table and insert my top 10 headlines into it.

Then, if you just want side by side results, i.e. :

1 | 2
3 | 4

You can do this :



SELECT * 
FROM
	#newsheadlines a
INNER JOIN #newsheadlines b ON a.id = b.id - 1
WHERE
	a.id % 2 = 1

Or if you want to go down the page and up again i.e. :

1 | 4
2 | 5
3 | 6

You can do something like :



SELECT * 
FROM
	#newsheadlines a
INNER JOIN #newsheadlines b ON b.id = 
a.id + ((Select count(1) FROM #newsheadlines) / 2)
WHERE
	a.id <= ((Select count(1) FROM #newsheadlines) / 2)

Damian

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-09 : 08:51:55

Smart frontier approach..

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-01-11 : 02:20:58

I knew about those first three stories, Damian, but that fourth one was news to me, althoug I'm not surprised.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-01-11 : 02:32:27

OK, Damian, really cool tricks with the joins, but what if the goal was to display the contents of a single text or long varchar database column formatted as two or more vertical columns on the page (and probably the contents of a different database column shown as the headline across the top, spanning multiple columns). I'm thinking that would definitely be a client-side thing, but any other fancy tricks up your sleeve?

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-01-11 : 02:35:46

That's crazy talk

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources