Sql Server Challenge -- can it be done?

  • I wrote a VB algorithm to do the following task but I wonder if it could be done in t-sql (I don't think so, but everytime I've thought that I've lived to regret it).

    We have 3 columns of strings. Column 1 is sorted (ascending) first. Column 2 is sorted for a given value in column 1.

    ab1

    ac2

    ac3

    db1

    dc2

    Here's the end result I want:

    ab1

    c2

    3

    db1

    d c2

    If a duplicate value occurs in the same colmun of two consecutive rows then replace the value in rows after the first with a blank or NULL.

    In the example, since "a" occurs in both row 1 and row 2, column 1, replace the "a" in row 2 column 1 with a blank. After doing that we have

    ab1

    c2

    ac3

    db1

    dc2

    But row 3, column 1 is also an "a" so replacing it by a blank gives:

    ab1

    c2

    c3

    db1

    dc2

    Next look at column 2, rows 1 and 2. They are different so we move on to the next row. Now column 2, rows 2 and

    3 are identical (="c") so the "c" in row 3 is replaced with a blank.

    ab1

    c2

    3

    db1

    dc2

    The pattern continues...until we reach the end result.

    TIA,

    For your time!

  • Post your actual code to do this in VB, and I'll write it in T-sql for ya....and yes, it can be done in T-sql.

    Edited by - scorpion_66 on 03/29/2003 08:34:43 AM

  • It's certainly possible in TSQL, you just need to loop through the table replacing the duplicate values.

    In the following example the values are inserted into another temp table (to set an identity field) before being returned:

    This is just one example way of doing it, I'm sure there are others (eg: using cursors), but it should give you the idea.

    -- Create table to hold original values

    declare @T table

    (

    col1 varchar(10),

    col2 varchar(10),

    col3 varchar(10)

    )

    -- populate

    insert into @T values('A','B','1')

    insert into @T values('A','C','2')

    insert into @T values('A','C','3')

    insert into @T values('D','B','1')

    insert into @T values('D','C','2')

    -- Create temp table to hold identity along side values for "While" code

    declare @Temp table

    (

    Ident int identity(1,1),

    col1 varchar(10),

    col2 varchar(10),

    col3 varchar(10)

    )

    -- populate Temp table

    insert into @Temp

    select * from @T

    -- create table to hold end results

    declare @Result table

    (

    col1 varchar(10),

    col2 varchar(10),

    col3 varchar(10)

    )

    -- declare variables for the "while" clause

    declare @Counter int

    declare @max-2 int

    declare @Col1 varchar(10)

    declare @Col2 varchar(10)

    declare @Col3 varchar(10)

    set @Counter = 1

    set @max-2 = (select max(ident) from @Temp)

    -- start cycle

    while @Counter <= @max-2

    begin

    if (select count(*) from @Result) = 0

    begin

    insert into @Result

    select col1,col2,col3 from @Temp where Ident = @Counter

    end

    else

    begin

    -- col1

    if (select col1 from @Temp where Ident = @Counter - 1)

    =

    (select col1 from @Temp where Ident = @Counter)

    begin

    set @Col1 = null

    end

    else

    begin

    set @Col1 = (select col1 from @Temp where Ident = @Counter)

    end

    -- col2

    if (select col2 from @Temp where Ident = @Counter - 1)

    =

    (select col2 from @Temp where Ident = @Counter)

    begin

    set @Col2 = null

    end

    else

    begin

    set @Col2 = (select col2 from @Temp where Ident = @Counter)

    end

    -- col3

    set @Col3 = (select col3 from @Temp where Ident = @counter)

    insert into @Result

    values (@Col1, @Col2, @Col3)

    end

    set @Counter = @Counter + 1

    end

    -- return results

    select * from @Result

  • quote:


    It's certainly possible in TSQL, you just need to loop through the table replacing the duplicate values.

    In the following example the values are inserted into another temp table (to set an identity field) before being returned:

    This is just one example way of doing it, I'm sure there are others (eg: using cursors), but it should give you the idea.

    -- Create table to hold original values

    declare @T table

    (

    col1 varchar(10),

    col2 varchar(10),

    col3 varchar(10)

    )

    -- populate

    insert into @T values('A','B','1')

    insert into @T values('A','C','2')

    insert into @T values('A','C','3')

    insert into @T values('D','B','1')

    insert into @T values('D','C','2')

    -- Create temp table to hold identity along side values for "While" code

    declare @Temp table

    (

    Ident int identity(1,1),

    col1 varchar(10),

    col2 varchar(10),

    col3 varchar(10)

    )

    -- populate Temp table

    insert into @Temp

    select * from @T

    -- create table to hold end results

    declare @Result table

    (

    col1 varchar(10),

    col2 varchar(10),

    col3 varchar(10)

    )

    -- declare variables for the "while" clause

    declare @Counter int

    declare @max-2 int

    declare @Col1 varchar(10)

    declare @Col2 varchar(10)

    declare @Col3 varchar(10)

    set @Counter = 1

    set @max-2 = (select max(ident) from @Temp)

    -- start cycle

    while @Counter <= @max-2

    begin

    if (select count(*) from @Result) = 0

    begin

    insert into @Result

    select col1,col2,col3 from @Temp where Ident = @Counter

    end

    else

    begin

    -- col1

    if (select col1 from @Temp where Ident = @Counter - 1)

    =

    (select col1 from @Temp where Ident = @Counter)

    begin

    set @Col1 = null

    end

    else

    begin

    set @Col1 = (select col1 from @Temp where Ident = @Counter)

    end

    -- col2

    if (select col2 from @Temp where Ident = @Counter - 1)

    =

    (select col2 from @Temp where Ident = @Counter)

    begin

    set @Col2 = null

    end

    else

    begin

    set @Col2 = (select col2 from @Temp where Ident = @Counter)

    end

    -- col3

    set @Col3 = (select col3 from @Temp where Ident = @counter)

    insert into @Result

    values (@Col1, @Col2, @Col3)

    end

    set @Counter = @Counter + 1

    end

    -- return results

    select * from @Result


    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • Here is a way to do this with a create and update statement against a temp table.

    -- Create table to hold original values

    declare @T table

    (

    col1 varchar(10),

    col2 varchar(10),

    col3 varchar(10)

    )

    -- populate

    insert into @T values('A','B','1')

    insert into @T values('A','C','2')

    insert into @T values('A','C','3')

    insert into @T values('D','B','1')

    insert into @T values('D','C','2')

    select * into #t from @T

    declare @col1 varchar(100)

    declare @col2 varchar(100)

    declare @col3 varchar(100)

    set @col1 = char(1)

    set @col2 = char(1)

    update #T

    set

    col1 = case when col1 <> substring(right(rtrim(@col1),2),1,1) then

    right(rtrim(@col1),1)

    else ' ' end,

    @col1 = rtrim(@col1) + col1,

    col2 = case when col2 <> substring(right(rtrim(@col2),2),1,1) then

    right(rtrim(@col2),1)

    else ' ' end,

    @col2 = rtrim(@col2) + col2

    select * from #t

    drop table #T

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

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

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