How to merge a column in single row in sql server [duplicate]

Question

I have a following data:

Comp_id |    Cntm_nb |  Seq_nb   |  Amount   |  Type 
   1    |      1     |    1      |  2000     | rent
   1    |      1     |    1      |  200      | charge
   1    |      1     |    2      |  3000     | rent
   1    |      1     |    2      |  300      | charge

from which i want to show the following result

Comp_id  |  Cntm_nb  | Seq_nb    | Rent Amount  |  Charge Amount   
   1     |     1     |   1       | 2000         |  200   
   1     |     1     |   2       | 3000         |  300 

Answer 1

You want to aggregate rows using a CASE condition within the aggregate.

SELECT Comp_id, Cntm_nb, Seq_nb,
       SUM((CASE WHEN Type='rent'   THEN Amount ELSE 0.0 END)) AS [Rent amount],
       SUM((CASE WHEN Type='charge' THEN Amount ELSE 0.0 END)) AS [Charge amount]
FROM xyz
GROUP BY Comp_id, Cntm_nb, Seq_nb;

The CASE within the aggregate function adds a condition, so if the row has the correct type, the amount is included in the aggregate, otherwise not. You could probably solve this using PIVOT if you want to, but the syntax is a bit trickier in my opinion (google it).

Answer 2

Here is a solution using PIVOT

 Declare @t table
 (
 Comp_id int,
 Cntm_nb int,
 Seq_nb int,
 Amount decimal(19,4),
 Type varchar(10)
 )

 Insert into @t
 values (1,1,1,2000,'rent'),
 (1,1,1,200,'charge'),
 (1,1,2,3000,'rent'),
 (1,1,2,300,'charge')

 Select Comp_id,
        Cntm_nb,
        Seq_nb,
        [rent] as [Rent Amount],
        [charge] as [Charge Amount]
 From @t
 PIVOT (SUM(Amount) for Type in ([rent],[charge]))as p