Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011
    Location
    Pune, India
    Posts
    7

    Unanswered: Combine Two Rows - SQL Query

    Dear All

    I have table as like below:

    Code:
    ID  Ref   Name      Type       Sal
    ----------------------------------------
    1   1     A         W          2000
    2   1     B         X          6000
    3   1     C         Y          3000
    4   1     D         Z          3000
    5   2     K         W          2000
    6   2     D         X          1000
    7   2     D         Y          1000
    8   2     D         Z          1000

    I want result like this:

    Code:
    ID  Ref    Name       Type        Sal
    ----------------------------------------
    1   1      A           W          8000
    3   1      C           Y          3000
    4   1      D           Z          3000
    5   2      K           W          3000
    7   2      D           Y          1000
    8   2      D           Z          1000

    As per the group of "Ref" field, in case of "Type" W, X "Sal" column needs to get sum of both the rows and keep the other fields from W row.

    In each Ref group (as like 1 or 2 in above data) there won't be duplication of "Type" field.

    How can I create SQL statement for get this result.

    All your help appreciated.


    Regards
    Somnath

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Like this (with Tabl0 being the name of the table in this example):
    Code:
    SELECT ID, Ref, Name, Type, Sal
    FROM Table0
    WHERE Type NOT IN ( 'W', 'X' )
    UNION
    SELECT b.ID, b.Ref, b.[Name], b.Type, a.s_Sal
    FROM Table0 AS b INNER JOIN (
        SELECT MIN(ID) AS m_ID, Ref, SUM(Sal) AS s_Sal
        FROM Table0
        WHERE Type IN ( 'W', 'X' )
        GROUP BY Ref)  AS a
    ON b.ID = a.m_ID
    ORDER BY ID;
    Have a nice day!

  3. #3
    Join Date
    Dec 2011
    Location
    Pune, India
    Posts
    7

    Combine Two Rows - SQL Query

    Thank you so much...!!!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •