SQL Server Performance

i have multiple records in one table but i want in a single record like

Discussion in 'SQL Server 2005 CLR' started by polaiah, Sep 13, 2007.

  1. polaiah New Member

    select yearofelection,con_code, con_name, name,VotesPolled, status from
    Election_history_Assembly E where 1= (select count(distinct votespolled) from Election_history_Assembly eh
    where e.yearofelection=eh.yearofelection and e.status=eh.status and e.con_code= eh.con_code and e.votespolled<=eh.votespolled)
    These is the result:
    yearofelection con_code con_name name VotesPolled status
    1983 1 ICHAPURAM M.V. KRISHNA RAO 28168 WON
    1983 1 ICHAPURAM S.L. RAO 19062 LOST
    1983 2 SOMPETA M. NARAYANA RAO 31314 WON
    1983 2 SOMPETA GOUTHU LACHANNA 27271 LOST
    1983 3 TEKKALI A. JANARDHAN RAO 35274 WON
    1983 3 TEKKALI S.L. NAIDU 15558 LOST

    i want the result in the form is:

    1983 1 ICHAPURAM M.V. KRISHNA RAO 28168 WON S.L. RAO 19062 LOST
    1983 2 SOMPETA M. NARAYANA RAO 31314 WON GOUTHU LACHANNA 27271 LOST
  2. Adriaan New Member

    So you have a separate record for who won, and who lost, and you want to see the two together on a single line in the results?
    The standard solution for this is to use a self-join, which is a join between two instances of the same table.
    SELECT t1.colA, t2.colA
    FROM MyTable t1
    INNER JOIN MyTable t2
    ON t1.key = t2.key
    WHERE t1.Status = 'LOST' AND t2.Status = 'WON'
    After ON, you put all the fields where the two rows must match ("yearofelection" and "con").
    After WHERE, you put all the fields where the two rows must be different ("status").

Share This Page