0

Please login or register to vote for this query.

(click on this box to dismiss)

Enter Query Description http://stackoverflow.com/questions/6881168/problem-on-pivot-table-in-sql-server

Q&A for pro webmasters

-- Enter Query Title
-- Enter Query Description

DECLARE  @newtest table (
  Section varchar(50),
  Department varchar(50), 
  salesamount float
)

Insert into @newtest
select 'Sec1','IT',2000.89 union
select 'Sec1','IT',1000.89 union
select 'Sec1','IT',3000.89 union
select 'Sec1','BPO',2000.89 union
select 'Sec2','BPO',5000.89 union
select 'Sec2','IT',2700.89 union
select 'Sec2','BPO',2000.89 union
select 'Sec3','IT',6000.89 union
select 'Sec3','IT',2000.89 union
select 'Sec3','BPO',9000.89 union
select 'Sec4','IT',2000.89 union
select 'Sec4','BPO',6000.89 union
select 'Sec3','BPO',1000.89 union
select 'Sec4','IT',3000.89

;WITH CTE AS (
select 
       Department,
       [Sec1]  ,
       [Sec2] ,
       [Sec3] ,
       [Sec4] 
  from (select Section, Department, salesamount 
          from @newtest) as Sourcetable
 PIVOT( SUM([salesamount])
      FOR [Section] IN ([Sec1],[Sec2],[Sec3],[Sec4]) ) AS Pivottable
)
SELECT 
a.sec1 Sec1bpo, a.sec2 sec2bpo, a.sec3 sec3bpo, a.sec4 sec4bpo,
b.sec1 Sec1it, b.sec2 sec2it, b.sec3 sec3it, b.sec4 sec4it
FROM cte a, cte b where a.department = 'BPO' and B.department = 'it'

fork query

created jul 30 11 conradfrix