Arrays

  • I am kinda new to querying.

    Scenario:

    I have two columns. (Item-Column1) and (Position-Column2).

    The Item column has many different duplicate records.

    I am trying to insert value in multiples of 10, in the Position column as it searches for each duplicate record (in column1).

    I remember doing this in college, but I need a little reminder... Thanks for any help!

    Example is below.

    Example:

    ~~~~~~~~~~

    Current:

    Column1 Column2

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

    ABC {null}

    ABC {null}

    XYZ {null}

    ABC {null}

    XYZ {null}

    XYZ {null}

    ABC {null}

    HIJ {null}

    ABC {null}

    ==================================

    After:

    Column1 Column2

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

    ABC 10

    ABC 20

    ABC 30

    ABC 40

    ABC 50

    XYZ 10

    XYZ 20

    XYZ 30

    HIJ 10

  • Hey,

    Try this code.

    Create table #name

    (

    Name varchar(5),

    Value int null

    )

    go

    insert into #name (Name)

    select 'ABC'

    union all

    select 'ABC'

    union all

    select 'ABC'

    union all

    select 'ABC'

    union all

    select 'ABC'

    union all

    select 'ABC'

    union all

    select 'ABC'

    union all

    select 'ABC'

    union all

    select 'ABC'

    union all

    select 'ABC'

    union all

    select 'ABC'

    union all

    select 'ABC'

    union all

    select 'ABC'

    union all

    select 'ABC'

    union all

    select 'ABC'

    union all

    select 'XYZ'

    union all

    select 'XYZ'

    union all

    select 'XYZ'

    union all

    select 'XYZ'

    union all

    select 'MNO'

    union all

    select 'PQR'

    union all

    select 'PQR'

    go

    select Name,count(Name) as Cnt

    into #Cnt

    from #name

    group by Name

    Select Name,N*10

    from #Cnt,Tally

    where N <= Cnt

    karthik

  • You can get by without creating the extra temp table...

    SELECT d.Name,t.N*10 AS DeciCount

    FROM (SELECT Name,COUNT(*) AS TheCount

    FROM #Name

    GROUP BY Name) d

    INNER JOIN dbo.Tally t

    ON t.N <= d.TheCount

    GoFigureFW... if you don't know what a Tally table is, please see the following link...

    http://www.sqlservercentral.com/articles/TSQL/62867/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks Jeff !

    karthik

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

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