Combine Two Rows - SQL Query

[Somnath_IT2006]

Dear All



I have table as like below:

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:

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

[Somnath_IT2006]

Hi All

I got the solution:

SELECT a.ID, a.Ref, a.Name1, a.Type, 
      IIF(a.Type ='W',(SELECT SUM(b.Sal) FROM tblTest b WHERE b.Ref = a.Ref AND (b.Type = 'W' OR b.Type = 'X')),a.Sal) as Sal1
FROM tblTest a WHERE a.Type <> 'X';

Regards
Somnath