Post reply

query

  • May 11, 2012 at 12:17 am

    #273886

    I have a table with two column

    out put date is like this...

    mnemonicId customerID

    1402 140201

    1402 140202

    1402 140203

    now i want out put like this

    mnemonicId customerID xyz

    1402 140201 140202

    1402 140201 140203

    1402 140202 140203

    so please help .....

  • May 11, 2012 at 12:30 am

    #1486108

    What is the logic here?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 11, 2012 at 12:51 am

    #1486115

    Sorry i havent any logic ....

  • May 11, 2012 at 1:02 am

    #1486116

    this will do the job with the sample data provided, but if you dont know the logic then we can only guess at what you want and how it should be done.

    declare @table table (mnemonicid int, customerid int)

    insert into @table values (1402,140201),(1402,140201),(1402,140202)

    ;with cte as

    (

    select

    mnemonicid,

    customerid,

    ROW_NUMBER() OVER(PARTITION BY customerid ORDER BY customerid) as RowNum

    from

    @table

    )

    select

    mnemonicid,

    customerid,

    customerid + rownum AS xyz

    from

    cte

  • May 11, 2012 at 1:06 am

    #1486117

    sachince61 (5/11/2012)

    Sorry i havent any logic ....

    Really? So any two customerIDs on the same line, like this?

    mnemonicId customerID xyz

    1402 140201 140202

    1402 140203 140201

    1402 140202 140201

    ?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 11, 2012 at 1:18 am

    #1486122

    sachince61 (5/11/2012)

    I have a table with two column

    out put date is like this...

    mnemonicId customerID

    1402 140201

    1402 140202

    1402 140203

    now i want out put like this

    mnemonicId customerID xyz

    1402 140201 140202

    1402 140201 140203

    1402 140202 140203

    so please help .....

    If there is no logic then why do you want to do it??...What is the business requirement behind this??

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • May 11, 2012 at 1:31 am

    #1486128

    anthony.green (5/11/2012)

    this will do the job with the sample data provided, but if you dont know the logic then we can only guess at what you want and how it should be done.

    declare @table table (mnemonicid int, customerid int)

    insert into @table values (1402,140201),(1402,140201),(1402,140202)

    ;with cte as

    (

    select

    mnemonicid,

    customerid,

    ROW_NUMBER() OVER(PARTITION BY customerid ORDER BY customerid) as RowNum

    from

    @table

    )

    select

    mnemonicid,

    customerid,

    customerid + rownum AS xyz

    from

    cte

    I tried the same thing Anthony.

    But then I checked out the sample data and the required result set posted by the OP and saw this difference in the last row:

    mnemonicId customerID

    1402 140201

    1402 140202

    1402 140203

    now i want out put like this

    mnemonicId customerID xyz

    1402 140201 140202

    1402 140201 140203

    1402 140202 140203

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • May 11, 2012 at 1:37 am

    #1486130

    vinu512 (5/11/2012)

    anthony.green (5/11/2012)

    this will do the job with the sample data provided, but if you dont know the logic then we can only guess at what you want and how it should be done.

    declare @table table (mnemonicid int, customerid int)

    insert into @table values (1402,140201),(1402,140201),(1402,140202)

    ;with cte as

    (

    select

    mnemonicid,

    customerid,

    ROW_NUMBER() OVER(PARTITION BY customerid ORDER BY customerid) as RowNum

    from

    @table

    )

    select

    mnemonicid,

    customerid,

    customerid + rownum AS xyz

    from

    cte

    I tried the same thing Anthony.

    But then I checked out the sample data and the required result set posted by the OP and saw this difference in the last row:

    mnemonicId customerID

    1402 140201

    1402 140202

    1402 140203

    now i want out put like this

    mnemonicId customerID xyz

    1402 140201 140202

    1402 140201 140203

    1402 140202 140203

    Excellent spot Vinu, I totally missed that, anyway if the OP doesnt know the logic or the business requirements to do the task then we can only speculate. One thing I would ask is that the OP follow the best practise links in our signatures on the next post with the logic so that we can help them better.

  • May 11, 2012 at 2:46 am

    #1486161

    Thanks for reply....

Viewing 9 posts - 1 through 8 (of 8 total)

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