SQLTeam.com | Weblogs | Forums

Need to get the below output using the input table in sql server

Below data is from sql server, need to get output data from the input table. Thanks in advance.......

​Input table:

|​Id |Company ​|​Name |Amount​ |​Designation |

|​1 |​x |​Alex |​5.00 |​Clerk |
|​1 |​x |​Thomas |​10.00 |​Manager |
|​1 |​y |​Bendrick |​25.00 |​Receptionist |
|​1 |​y |​George |​20.00 |​Manager |
|​1 |​asd |​Martin |​54.00 |​Receptionist |
|​2 |y |​Max |​96.00 |​Clerk |
|​2 |​y |​John |​4.00 |​Receptionist |
|​3 |​esd |​Ben |​15.00 |​Clerk |
|​3 |​cxz |​Long |​60.00 |​Manager |
|​4 |​x |​Bendrick |​29.00 |​Receptionist |
|​4 |​y |​Thomas |​30.00 |​Clerk |
|​5 |​jkl |​Alex |​1.00 |​Receptionist |

From the above table, First we need to group by Id then we need to group by Company. Here we need to group by Company x and y only, for other values we need to pass Null for varchar values and 0.00 for Amount fields. For Name, Designation fields we need to use max condition and Amount field we need to use sum condition.

​Output table:

|​Id |​Company |Name ​|Amount​ |Designation ​|

|​1 |​x |​Thomas |​15.00 |​Manager |
|1 ​|​y |​George |​45.00 |​Manager |
|​1 |​Null |​Null |0.00​ |​Null |
|2 ​|​y |​Max |​100.00 |​Clerk |
|​3 |​Null |Null |0.00​ |Null​ |
|​4 |​x |​Bendrick |​29.00 |​Receptionist |
|​4 |​y |​Thomas |​30.00 |​Clerk |
|​5 |Null​ |​Null |0.00 ​|​Null |

select ID ,
Company = case when Company in ('x','y') then Company end,
Name = max(case when Company in ('x','y') then Name end) ,
Amount = sum(case when Company in ('x','y') then Amount else 0 end) ,
Designation = max(case when Company in ('x','y') then Designation end)
from tbl
group by ID, case when Company in ('x','y') then Company end