SQLTeam.com | Weblogs | Forums

Query - results returned in 3 different ways


#1

Given this data:

apple 22222 Fred
baker 11111 Jim
chuck 33333 David

I'd like the query to return this:
( -- sorted by col 1 --) (-- sorted by col 2 --) (-- sorted by col 3 --)
apple 22222 Fred baker 11111 Jim chuck 33333 David
baker 11111 Jim apple 22222 Fred apple 22222 Fred
chuck 33333 David chuck 33333 David baker 11111 Jim

This data then gets fed into a report generator... as long as I can get the data in the format I need it'll come out looking just find.

Thanks in advance for any help.


#2
;WITH cte AS
(
	SELECT *,
		ROW_NUMBER() OVER (ORDER BY col1) RN1,
		ROW_NUMBER() OVER (ORDER BY col2) RN2,
		ROW_NUMBER() OVER (ORDER BY col3) RN3
	FROM
		Tbl
)
SELECT
	a.col1,a.col2,a.col3,
	b.col1,b.col2,b.col3,
	c.col1,c.col2,c.col3
FROM
	cte a
	INNER JOIN cte b ON a.RN1 = b.RN2
	INNER JOIN cte c ON a.RN1 = c.RN3
ORDER BY
	a.RN1;