• 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!