Post reply

Query Help

  • December 5, 2014 at 12:11 am

    #295657

    Hi ,

    I know this could be simple but i am not getting it. Could anybody please help me with his.

    Consider the below code snippet

    CREATE TABLE #TEMP(City1 varchar(20),City2 varchar(20),Distance int)

    INSERT INTO #TEMP VALUES('X','Y',60)

    INSERT INTO #TEMP VALUES('Y','X',60)

    INSERT INTO #TEMP VALUES('A','B',70)

    INSERT INTO #TEMP VALUES('B','A',70)

    INSERT INTO #TEMP VALUES('C','D',80)

    INSERT INTO #TEMP VALUES('D','C',80)

    I am trying to get the result as

    City1 City2 Distance

    X Y 60

    A B 70

    C D 80

  • December 5, 2014 at 12:35 am

    #1762868

    What is your business logic? Why would you select (X,Y,60) and not (Y,X,60)?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • December 5, 2014 at 2:29 am

    #1762887

    There are many ways to do that. For example

    select c1 = case when City2 > City1 then City1 else City2 end

    , c2 = case when City2 > City1 then City2 else City1 end

    -- may be min() ?

    , d = max(Distance)

    from #TEMP

    group by case when City2 > City1 then City1 else City2 end

    ,case when City2 > City1 then City2 else City1 end

  • December 5, 2014 at 2:33 am

    #1762889

    Koen Verbeeck (12/5/2014)

    What is your business logic? Why would you select (X,Y,60) and not (Y,X,60)?

    Well, there is no business logic. It can be anything, i was trying out this for fun, since last 1 day 🙂

  • December 5, 2014 at 3:04 am

    #1762898

    serg-52 (12/5/2014)

    There are many ways to do that. For example

    select c1 = case when City2 > City1 then City1 else City2 end

    , c2 = case when City2 > City1 then City2 else City1 end

    -- may be min() ?

    , d = max(Distance)

    from #TEMP

    group by case when City2 > City1 then City1 else City2 end

    ,case when City2 > City1 then City2 else City1 end

    As you said there are many ways, could you please hint out that what are other ways by which we can do this e.g.we can do this using XXXX (e.g. Pivot table)

  • December 5, 2014 at 3:47 am

    #1762909

    Shadab Shah (12/5/2014)

    Koen Verbeeck (12/5/2014)

    What is your business logic? Why would you select (X,Y,60) and not (Y,X,60)?

    Well, there is no business logic. It can be anything, i was trying out this for fun, since last 1 day 🙂

    🙂

    select * from #TEMP where City1< City2

    select min(city1), max(city2), distance

    from #TEMP

    group by distance

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

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