Post reply

Query Help

  • May 27, 2013 at 6:34 am

    #294516

    Hi,Can some one throw some light on how to approach this....

    CREATE TABLE A (ID1 VARCHAR(10),ID2 VARCHAR(10))

    INPUT

    TABLE A

    Id1 ID2

    A B

    B C

    D E

    F G

    and I want the Output to be as follows

    ID1 ID2

    A C

    B E

    D G

    Thanks.....

  • May 27, 2013 at 6:37 am

    #1618773

    Do you have any other column in the table?

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • May 27, 2013 at 6:41 am

    #1618774

    No..thats the only one I have....

    Thanks

  • May 27, 2013 at 6:43 am

    #1618775

    No..thats the only one I have....

    Thanks

  • May 27, 2013 at 6:49 am

    #1618776

    No..thats the only one I have....

    Thanks

  • May 27, 2013 at 6:52 am

    #1618777

    ravi01031986 (5/27/2013)

    No..thats the only one I have....

    Thanks

    You can try this, but I am not sure if this will give the same result all the time

    You will need some column which decides the order of the rows in the table to ensure same results all the time

    ; WITH cte_table AS

    (

    SELECTROW_NUMBER() OVER( ORDER BY ( SELECT NULL ) ) AS RN, *

    FROM

    AS T

    )

    SELECTT1.ID1, T2.ID2

    FROMcte_table AS T1

    INNER JOIN cte_table AS T2 ON T1.RN = T2.RN - 1

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • May 27, 2013 at 7:05 am

    #1618779

    You'll need to add a rownumber to the table so you can JOIN it to the previous row. In the below code I've used also a CTE . That way I only have to define the rownumber once to the table and use it multiple times in the final query.

    create table #test1 (id varchar(10), id2 varchar(10))

    insert #test1

    select 'A', 'B'

    UNION ALL

    select 'B', 'C'

    UNION ALL

    select 'D', 'E'

    UNION ALL

    select 'F', 'G'

    ;with cte_rownumber as

    (SELECT

    ROW_NUMBER() OVER (ORDER BY id) as rownr

    , ID

    , id2

    from #test1)

    select

    current_row.id

    , next_row.id2

    from cte_rownumber as current_row

    inner join cte_rownumber as next_row

    on current_row.rownr = next_row.rownr - 1

    drop table #test1

    EDITED:

    I see Kingston Dhasian just beet me to it with the same solution 😛

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • May 27, 2013 at 7:14 am

    #1618782

    thanks for your help....

  • May 28, 2013 at 1:39 am

    #1618872

    I am trying to do the same with Self Join.Can someone help me out...

    CREATE TABLE Test 1 (Id1 Varchar(10), Id2 Varchar(10))

    INSERT Test1 SELECT 'A','B'

    INSERT Test1 SELECT 'B','C'

    INSERT Test1 SELECT 'C','D'

    CREATE TABLE Test 2(Id1 Varchar(10), Id2 Varchar(10))

    INSERT Test2 SELECT 'A','B'

    INSERT Test2 SELECT 'B','C'

    INSERT Test2 SELECT 'C','D'

    SELECT T1.ID1, T2.ID2

    FROM Test T1 ,Test T2

    ON T1.ID1 = T2.ID1

    Thanks...

  • May 28, 2013 at 1:49 am

    #1618873

    You should number your rows (add a number column) and join on this number column, or change your current JOIN statement from "ON T1.ID1 = T2.ID1" to "ON T1.ID1 = T2.ID2"

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • May 28, 2013 at 2:13 am

    #1618878

    Thanks .....

Viewing 11 posts - 1 through 10 (of 10 total)

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