CODEWITHSUNDEEP
sql-serverpivot
Calgar99
Calgar99

Reputation: 1688

How do I group pivot columns?

I have a table in SQL Server with the following entries.

Name    Type        Amount
------------------------------
ABC     Opening     100 
CBD     Invoice     200 
ABC     Spreadsheet 250 
FBD     Closing     400

I am trying to create a pivot table based on the above however, I'm also trying to divide the Type column into 3 different columns.

See table below. Is this possible?

Name    Opening  Activity   Closing
----------------------------------------
ABC     100          200          0
CBD       0          250          0
FBD       0            0        400

Code so far

select *
from
    (
        select  [Name]
                ,[Type]
                ,[Amount]
        from my_Table
    ) a
    pivot(sum(Amount)
    for Type in (Opening], [Closing])
    )as pvt

How do I go about doing this?

Upvotes: 0

Views: 44

Answers (1)

Taryn
Taryn

Reputation: 247710

I'd suggest doing this with an aggregate function CASE expression:

select 
  name,
  sum(case when type = 'Opening' then Amount else 0 end) Opening,
  sum(case when type not in ('Opening', 'Closing') then Amount else 0 end) Activity,
  sum(case when type = 'Closing' then Amount else 0 end) Closing
from my_table
group by name;

See SQL Fiddle with Demo. You'd use the CASE logic to look specifically for the Opening and Closing values, and then the final column would sum the rows where the Type is not Opening or Closing.

You could use PIVOT to get the result, you'll just need to associate the "other" activities before you apply the pivot function. You can do this using a subquery:

select name, Opening, Activity, Closing
from
(
  select 
    name,
    type = case 
              when type not in ('Opening', 'Closing') 
              then 'Activity'
              else type
            end,
    amount
  from my_table
) d
pivot
(
  sum(amount)
  for type in (Opening, Activity, Closing)
)piv;

See SQL Fiddle with Demo

Upvotes: 3

Related Questions