CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
vidooo
vidooo

Reputation: 135

Convert rows to columns with pivoting

I am learning SQL and I want to make a query that makes new columns which are made of values from a table. I have a column named transactions and there are multiple transactions by the same company. Table looks something like this:

Id  Name   Payd
1   John   5.00
2   Adam   5.00
3   John   10.00
4   John   10.00
5   Adam   15.00

I want to make something like this:

Id  Name   5.00 10.00 15.00 Sum
1   John   5.00 20.00 0     25.00
2   Adam   5.00 0     15.00 20.00

I was considering doing it with PIVOT function but I'm having trouble with the implementation. My code looks something like this:

(select emplployer, CAST (4.00 as decimal(10,0)) as [4.00],
CAST (5.00 as decimal(10,0)) as [5.00],
CAST (10.00 as decimal(10,0)) as [10.00],
CAST (18.00 as decimal(10,0)) as [18.00],
CAST (20.00 as decimal(10,0)) as [20.00]
from (select Name, cast(Payd as decimal(10,0)) as summ from employee) q1
pivot
(
    sum(summ) for employer in ([4.00], [5.00], [10.00], [18.00], [20.00])
)pvt;

Upvotes: 3

Views: 84

Answers (1)

Matt
Matt

Reputation: 14341

Conditional Aggregation Method:

SELECT
    Name
    ,SUM(CASE WHEN Payd = 5 THEN Payd ELSE 0 END) as [5.00]
    ,SUM(CASE WHEN Payd = 10 THEN Payd ELSE 0 END) as [10.00]
    ,SUM(CASE WHEN Payd = 15 THEN Payd ELSE 0 END) as [15.00]
    ,SUM(Payd) as [Sum]
FROM
    @Employees
GROUP BY
    Name

One Way to Do it using Pivot:

;WITH cte AS (
    SELECT
       Name
       ,Payd
       ,Payd as PaydColNames
    FROM
       @Employees
)

SELECT
    Name
    ,[5.00] = ISNULL([5.00],0)
    ,[10.00] = ISNULL([10.00],0)
    ,[15.00] = ISNULL([15.00],0)
    ,[Sum] = ISNULL([5.00],0) + ISNULL([10.00],0) + ISNULL([15.00],0)
FROM
    cte
    PIVOT (
       SUM(Payd) FOR PaydColNames IN ([5.00],[10.00],[15.00])
    ) p

The problem you appear to be having is you are attempting to use the same column to Pivot on that you Aggregate which doesn't give you the desired result. So you have to replicate the Payd column so you can use the data as both the PIVOT and the Aggregate. Then because you desire the 0 when the value is NULL you have to use ISNULL or COALESCE to eliminate the NULL. In my opinion Conditional Aggregation will serve you better for a use like this.

Test Data

DECLARE @Employees AS TABLE (Id INT, Name VARCHAR(50), Payd MONEY)
INSERT INTO @Employees VALUES
(1,'John',5.00)
,(2,'Adam',5.00)
,(3,'John',10.00)
,(4,'John',10.00)
,(5,'Adam',15.00)

Upvotes: 3

Related Questions