distribute evenly a single column data to multiple column

  • Lambert Antonio

    SSChasing Mays

    Points: 640

    i have a table with the following data

    r1

    r2

    r3

    r4

    r5

    r6

    r7

    i wanted to display it in a grid with 3 columns

    r1 r2 r3

    r4 r5 r6

    r7

    i can do this with cursor (code below) but was wondering if there's a more elegant way of doing it

    declare @t1 table(refno varchar(50))

    insert into @t1

    select 'r1' union

    select 'r2' union

    select 'r3' union

    select 'r4' union

    select 'r5' union

    select 'r6' union

    select 'r7'

    declare @t2 table(rowId int, refno1 varchar(50), refno2 varchar(50), refno3 varchar(50))

    declare @row int

    declare @col int

    set @row = 1

    set @col = 1

    declare @refno varchar(50)

    declare cur CURSOR FOR

    select refno from @t1

    open cur

    fetch next from cur

    into @refno

    while @@FETCH_STATUS = 0

    begin

    if @col = 1

    insert into @t2 (rowId, refno1, refno2, refno3)

    values (@row, @refno, null, null)

    else if @col = 2

    update @t2 set refno2 = @refno

    where rowId = @row

    else

    begin

    update @t2 set refno3 = @refno

    where rowId = @row

    set @row = @row + 1

    set @col = 0

    end

    set @col = @col + 1

    fetch next from cur

    into @refno

    end

    close cur

    deallocate cur

    select refno1, refno2, refno3 from @t2


    slow down when you need to hurry, stop when you need to move on,
    look back when you need to forget, or you might slip and leave sanity

  • Cadavre

    SSC-Forever

    Points: 41690

    Here's one way: -

    --Your sample data (Thanks, makes it much easier when readily consumable sample data exists)

    DECLARE @t1 TABLE (refno VARCHAR(50));

    INSERT INTO @t1

    SELECT 'r1'

    UNION SELECT 'r2'

    UNION SELECT 'r3'

    UNION SELECT 'r4'

    UNION SELECT 'r5'

    UNION SELECT 'r6'

    UNION SELECT 'r7';

    DECLARE @t2 TABLE (rowId INT, refno1 VARCHAR(50), refno2 VARCHAR(50), refno3 VARCHAR(50));

    --Actual solution

    INSERT INTO @t2

    SELECT N,

    NULLIF(MAX(CASE WHEN Y = 1 THEN refno ELSE '' END),'') AS refno1,

    NULLIF(MAX(CASE WHEN Y = 2 THEN refno ELSE '' END),'') AS refno2,

    NULLIF(MAX(CASE WHEN Y = 3 THEN refno ELSE '' END),'') AS refno3

    FROM (SELECT refno, ROW_NUMBER() OVER(PARTITION BY N ORDER BY (SELECT NULL)), N

    FROM (SELECT refno, (X/3)+1, X

    FROM (SELECT refno,

    ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1

    FROM @t1) a(refno, X)

    ) b(refno, N, X)

    ) y(refno, Y, N)

    GROUP BY N;

    --Check results

    SELECT * FROM @t2;

    Produces: -

    rowId refno1 refno2 refno3

    ----------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------

    1 r1 r2 r3

    2 r4 r5 r6

    3 r7 NULL NULL


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Lambert Antonio

    SSChasing Mays

    Points: 640

    thanks Cadavre


    slow down when you need to hurry, stop when you need to move on,
    look back when you need to forget, or you might slip and leave sanity

  • Cadavre

    SSC-Forever

    Points: 41690

    Lambert Antonio (3/21/2012)


    thanks Cadavre

    No problem.

    In case you're interested, the cursor is also massively outperformed by my method as well.

    See: -

    IF object_id('tempdb..#testEnvironment') IS NOT NULL

    BEGIN

    DROP TABLE #testEnvironment;

    END;

    --1,000,000 Random rows of data

    SELECT TOP 1000000

    'r' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(7)) AS refno

    INTO #testEnvironment

    FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

    IF object_id('tempdb..#testEnvironment2') IS NOT NULL

    BEGIN

    DROP TABLE #testEnvironment2;

    END;

    --Actual solution

    SELECT N,

    NULLIF(MAX(CASE WHEN Y = 1 THEN refno ELSE '' END),'') AS refno1,

    NULLIF(MAX(CASE WHEN Y = 2 THEN refno ELSE '' END),'') AS refno2,

    NULLIF(MAX(CASE WHEN Y = 3 THEN refno ELSE '' END),'') AS refno3

    INTO #testEnvironment2

    FROM (SELECT refno, ROW_NUMBER() OVER(PARTITION BY N ORDER BY (SELECT NULL)), N

    FROM (SELECT refno, (X/3)+1, X

    FROM (SELECT refno,

    ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1

    FROM #testEnvironment) a(refno, X)

    ) b(refno, N, X)

    ) y(refno, Y, N)

    GROUP BY N;

    --Check results

    SELECT * FROM #testEnvironment2;

    On my system, that takes around 6.3 seconds (half of which is the time taken to actually select the 333,334 results from the new table) to split 1 million rows into your 3 partitions.

    If we do the same thing with your cursor: -

    IF object_id('tempdb..#testEnvironment') IS NOT NULL

    BEGIN

    DROP TABLE #testEnvironment;

    END;

    --1,000,000 Random rows of data

    SELECT TOP 1000000

    'r' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(7)) AS refno

    INTO #testEnvironment

    FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

    IF object_id('tempdb..#testEnvironment2') IS NOT NULL

    BEGIN

    DROP TABLE #testEnvironment2;

    END;

    CREATE TABLE #testEnvironment2 (rowId int, refno1 varchar(50), refno2 varchar(50), refno3 varchar(50))

    declare @row int

    declare @col int

    set @row = 1

    set @col = 1

    declare @refno varchar(50)

    declare cur CURSOR FOR

    select refno from #testEnvironment

    open cur

    fetch next from cur

    into @refno

    while @@FETCH_STATUS = 0

    begin

    if @col = 1

    insert into #testEnvironment2 (rowId, refno1, refno2, refno3)

    values (@row, @refno, null, null)

    else if @col = 2

    update #testEnvironment2 set refno2 = @refno

    where rowId = @row

    else

    begin

    update #testEnvironment2 set refno3 = @refno

    where rowId = @row

    set @row = @row + 1

    set @col = 0

    end

    set @col = @col + 1

    fetch next from cur

    into @refno

    end

    close cur

    deallocate cur

    select * from #testEnvironment2

    Well, I cancelled it after 2 minutes 30 seconds because I was bored of waiting 😛


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • tfifield

    SSCrazy Eights

    Points: 9655

    Antonio,

    Here is a solution that is a bit easier to read than Cadavre's solution. It doesn't perform quite as well on large record sets, however.

    It's just another option.

    CREATE TABLE #T

    ( RefNo VARCHAR(50))

    GO

    INSERT INTO #T

    SELECT 'r1'

    UNION SELECT 'r2'

    UNION SELECT 'r3'

    UNION SELECT 'r4'

    UNION SELECT 'r5'

    UNION SELECT 'r6'

    UNION SELECT 'r7';

    GO

    WITH RefOrdered AS

    ( SELECT RefNo

    , ROW_NUMBER() OVER (ORDER BY RefNo) AS RN

    FROM #T

    )

    -- Get the column for the RefNo (1 - 3)

    , ColOrd AS

    ( SELECT RefNo, RN

    , CASE WHEN RN % 3 = 0 THEN 3 ELSE RN % 3 END AS ColNum

    FROM RefOrdered

    )

    -- If you subtract the ColNum (1 - 3) from the absolute row number

    -- you get distinct groups - i.e. 0, 3, 6. These act as an

    -- anchor for the final output row

    SELECT

    MAX(CASE WHEN ColNum = 1 THEN RefNo ELSE '' END) AS RefNo1

    , MAX(CASE WHEN ColNum = 2 THEN RefNo ELSE '' END) AS RefNo2

    , MAX(CASE WHEN ColNum = 3 THEN RefNo ELSE '' END) AS RefNo3

    FROM ColOrd

    GROUP BY (RN - ColNum)

    ORDER BY (RN - ColNum)

    Todd Fifield

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply