my guess is he wants to turn the output into multiple columns in order to save space; instead of one long three column list, he wants two columns of the same data side by side.
i've got this example, which spreads accross five columns, as an example:
declare @divisor int
select @divisor = (count(*) / 5 ) + 1 from sys.tables
;with baseCTE AS (select ROW_NUMBER() over (order by name) As RW,name from sys.tables),
firstCTE AS (select RW / @divisor as RW1, name from baseCTE),
G1 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 0),
G2 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 1),
G3 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 2),
G4 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 3),
G5 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 4)
SELECT
G1.name,
G2.name,
G3.name,
G4.name,
G5.name
FROM G1
LEFT OUTER JOIN G2 ON G1.RW = G2.RW
LEFT OUTER JOIN G3 ON G1.RW = G3.RW
LEFT OUTER JOIN G4 ON G1.RW = G4.RW
LEFT OUTER JOIN G5 ON G1.RW = G5.RW
--the correct example:
WITH
baseCTE AS
(
SELECT TOP(24*5)
(ROW_NUMBER() OVER (ORDER BY Name)-1)/5+1 AS RW,
(ROW_NUMBER() OVER (ORDER BY Name)-1)%5+1 AS CL,
Name
FROM sys.tables
)
SELECT MAX(CASE WHEN CL = 1 THEN Name ELSE '' END) AS Col1TableName,
MAX(CASE WHEN CL = 2 THEN Name ELSE '' END) AS Col2TableName,
MAX(CASE WHEN CL = 3 THEN Name ELSE '' END) AS Col3TableName,
MAX(CASE WHEN CL = 4 THEN Name ELSE '' END) AS Col4TableName,
MAX(CASE WHEN CL = 5 THEN Name ELSE '' END) AS Col5TableName
FROM baseCTE
GROUP BY RW
;
Lowell