CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
user870337
user870337

Reputation: 69

Problem on Pivot table in SQL server

create table newtest (
  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

select * from newtest

  Select section, department, SUM(salesamount) 
    from newtest 
Group by Section, Department

--use of pivot

select [Sec1] AS Sec1BPO,
       [Sec2] AS Sec2BPO,
       [Sec3] AS Sec3BPO,
       [Sec4] AS Sec4BPO,
       [Sec1] AS Sec1IT,
       [Sec2] AS Sec2IT,
       [Sec3] AS Sec3IT,
       [Sec4] AS Sec4IT
  from (select Section, Department, salesamount 
          from newtest) as Sourcetable
 PIVOT( SUM([salesamount])
      FOR [Section] IN ([Sec1],[Sec2],[Sec3],[Sec4]) ) AS Pivottable
ORDER BY Department

Please help me to resolve the issue....I got the result when execute above code as

Sec1BPO  Sec2BPO  Sec3BPO   Sec4BPO  Sec1IT   Sec2IT   Sec3IT    Sec4IT
-------------------------------------------------------------------------
2000.89  7001.78  10001.78  6000.89  2000.89  7001.78  10001.78  6000.89
6002.67  2700.89  8001.78   5001.78  6002.67  2700.89  8001.78   5001.78

but I need as

Sec1BPO  Sec2BPO  Sec3BPO   Sec4BPO  Sec1IT   Sec2IT   Sec3IT   Sec4IT
-----------------------------------------------------------------------
2000.89  7001.78  10001.78  6000.89  6002.67  2700.89  8001.78  5001.78

Please Help me to get desired result.

Upvotes: 3

Views: 865

Answers (2)

Andriy M
Andriy M

Reputation: 77657

You could try something like this:

select Sec1BPO,
       Sec2BPO,
       Sec3BPO,
       Sec4BPO,
       Sec1IT,
       Sec2IT,
       Sec3IT,
       Sec4IT
  from (select Section + Department AS SectDept, salesamount 
          from newtest) as Sourcetable
 PIVOT( SUM([salesamount])
      FOR SectDept IN (Sec1BPO, Sec2BPO, Sec3BPO, Sec4BPO,
                       Sec1IT,  Sec2IT,  Sec3IT,  Sec4IT) ) AS Pivottable
ORDER BY 1

UPDATE

Just one word of caution – the above solution, when applied in general, might put you at risk of summing together pieces of data that were not meant to be done so. That is, taking your present example, if it contained rows with both Sec1, BPO and Sec1B, PO as combinations of Section and Department, both combinations would end up concatenated as Sec1BPO and thus added up together, which would obviously be wrong.

Therefore, some delimiter should probably be used when concatenating the parts to exclude such duplicates, like space or a different character that is known for sure to be absent from the values to be concatenated. I mean something like this:

select [Sec1 BPO],
       [Sec2 BPO],
       [Sec3 BPO],
       [Sec4 BPO],
       [Sec1 IT],
       [Sec2 IT],
       [Sec3 IT],
       [Sec4 IT]
  from (select Section + ' ' + Department AS SectDept, salesamount 
          from newtest) as Sourcetable
 PIVOT( SUM([salesamount])
      FOR SectDept IN ([Sec1 BPO], [Sec2 BPO], [Sec3 BPO], [Sec4 BPO],
                       [Sec1 IT],  [Sec2 IT],  [Sec3 IT],  [Sec4 IT])
      ) AS Pivottable
ORDER BY 1

(Thanks @Conrad Frix for raising the issue.)

Upvotes: 2

Conrad Frix
Conrad Frix

Reputation: 52645

;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'​

to see it working go to the Data.SE query SO Question 6881168

Note I used a Table variable instead of a table because Create table doesn't work on Data.SE

Output

Sec1bpo sec2bpo sec3bpo  sec4bpo Sec1it  sec2it  sec3it  sec4it  
------- ------- -------- ------- ------- ------- ------- ------- 
2000.89 7001.78 10001.78 6000.89 6002.67 2700.89 8001.78 5001.78

Upvotes: 1

Related Questions