Organizing Result Set - SQL Server Forums

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

SQL Server Development (2000)

Organizing Result Set

Author

Topic

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-06-06 : 13:06:56

I'm trying to do a dynamic image gallery. If I do a select statement from a table and get my list of pictures, how can I set up a loop that will put the pictures in rows of like 4, then move to a new row?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-06 : 14:15:03

You don't really need a loop to do this. An ordinary set based query will suffice.

This example presumes your IDENTITY column is named PictID and the path to the picture is named Path.

Use the following code for zero-based IDENTITY

SELECT		PictID / 4 PictRow,
		MAX(CASE WHEN PictID % 4 = 0 THEN Path ELSE NULL END) Col0,
		MAX(CASE WHEN PictID % 4 = 1 THEN Path ELSE NULL END) Col1,
		MAX(CASE WHEN PictID % 4 = 2 THEN Path ELSE NULL END) Col2,
		MAX(CASE WHEN PictID % 4 = 3 THEN Path ELSE NULL END) Col3
FROM		PicTable
GROUP BY	PictID / 4
ORDER BY	PictID / 4

And use following code for one-based IDENTITY

SELECT		(PictID - 1) / 4 PictRow,
		MAX(CASE WHEN (PictID - 1) % 4 = 0 THEN Path ELSE NULL END) Col0,
		MAX(CASE WHEN (PictID - 1) % 4 = 1 THEN Path ELSE NULL END) Col1,
		MAX(CASE WHEN (PictID - 1) % 4 = 2 THEN Path ELSE NULL END) Col2,
		MAX(CASE WHEN (PictID - 1) % 4 = 3 THEN Path ELSE NULL END) Col3
FROM		PicTable
GROUP BY	(PictID - 1) / 4
ORDER BY	(PictID - 1) / 4

Or to be on the safe side, for example if there is many gaps in the Picture IDENTITY column,

DECLARE @t TABLE (ID INT IDENTITY (0, 1), Path VARCHAR (256))

INSERT @t (Path)
SELECT	Path
FROM	PictTable
ORDER BY PictID

SELECT		ID / 4 PictRow,
		MAX(CASE WHEN ID % 4 = 0 THEN Path ELSE NULL END) Col0,
		MAX(CASE WHEN ID % 4 = 1 THEN Path ELSE NULL END) Col1,
		MAX(CASE WHEN ID % 4 = 2 THEN Path ELSE NULL END) Col2,
		MAX(CASE WHEN ID % 4 = 3 THEN Path ELSE NULL END) Col3
FROM		@t
GROUP BY	ID / 4
ORDER BY	ID / 4

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-09 : 04:17:44

Any luck with this problem?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources