CODEWITHSUNDEEP
sqlsql-serverdatabase
Lali
Lali

Reputation: 2866

SQL Server: Get sum based on Id from single column

I have a table as

enter image description here

I need result as

enter image description here

I only have two CaseNo, 1 and 2. How can I do this in single query, not common table expression or separate queries?

Upvotes: 0

Views: 130

Answers (2)

Dvintila
Dvintila

Reputation: 212

SELECT 'Amount' , 
 [1], [2]
FROM
(SELECT CaseNo, Amount
    FROM table) AS SourceTable
PIVOT
(
sum(Amount)
FOR CaseNo IN ( [1], [2])
) AS PivotTable;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can use conditional aggregation:

select 1 as CaseNo1, sum(case when CaseNo = 1 then Amount end) as Amount1,
       2 as CaseNo2, sum(case when CaseNo = 2 then Amount end) as Amount2
from table;

However, I would not recommend doing this transformation in SQL. If you need the data in this format in an application, you should probably do it in the application layer.

Upvotes: 3

Related Questions