distribute evenly a single column data to multiple column

  • 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

  • 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/

  • 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

  • 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/

  • 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

    ( RefNoVARCHAR(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