SQLTeam.com | Weblogs | Forums

I am confused on pivot tables

sql2012

#1

am trying to change some of the rows to columns. I read how I can do it using Pivot, but I am not understanding it clearly how.

This is what I have

nCustNum    Amt             Subv_Chan_Type
1000013   -80.05            (364408)
1000013   -20.47            (364418)
1000013   -5.89             (364428)
1000055   -105.31           (364408)
1000055   -19.335           (364418)
1000055   -6.555555         (364428)
1000070    0                (364408)
1000070    0                (364418)
1000070    0                (364428)
1000071   -96.0189740000001 (364408)

This is what I want it to be:

nCustNum    (364408)    (364418)     (364428)    
1000013      -80.05   -20.47    -5.89      
1000055     -105.31   -19.335   -6.55555 
1000070      0          0         0
1000071     -96.018     0         0

#2

Try this:-

drop table test1

create table test1 (
nCustNum int,
Amt float ,
Subv_Chan_Type varchar(50))

insert into test1
select 1000013 as nCustNum , -80.05 as Amt,(364408) as Subv_Chan_Type
union all
select 1000013 as nCustNum , -20.47 as Amt,(364418) as Subv_Chan_Type
union all
select 1000013 as nCustNum , -5.89 as Amt,(364428)as Subv_Chan_Type
union all
select 1000055 as nCustNum , -105.31 as Amt,(364408)as Subv_Chan_Type
union all
select 1000055 as nCustNum , -19.335 as Amt,(364418)as Subv_Chan_Type
union all
select 1000055 as nCustNum , -6.555555as Amt,(364428)as Subv_Chan_Type
union all
select 1000070 as nCustNum ,0 as Amt,(364408)as Subv_Chan_Type
union all
select 1000070 as nCustNum ,0 as Amt,(364418)as Subv_Chan_Type
union all
select 1000070 as nCustNum ,0 as Amt,(364428)as Subv_Chan_Type
union all
select 1000071 as nCustNum , -96.0189740000001 as Amt,(364408)as Subv_Chan_Type

/*
select * from test1
*/

select
nCustNum
,isnull([364408],0) as [364408]
,isnull([364418],0) as [364418]
,isnull([364428],0) as [364428]
from (

select * from test1) p pivot( sum(Amt) for Subv_Chan_Type

in (
[364408]
,[364418]
,[364428]
))u