在SQL Server中的数据透视表问题

Question

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

--使用数据透视表

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

请帮我解决这个问题...当执行上面的代码时，我得到了以下结果

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

但是我需要作为一个。

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

请帮我获得所需的结果。

- user870337

Answer 1

你可以尝试这样做：

你可以尝试这样做：

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

---

更新

需要注意的是，上述解决方案在普遍应用时可能会使您面临将不应该合并的数据片段相加的风险。也就是说，以您目前的示例为例，如果它包含既有 Sec1、BPO 又有 Sec1B、PO 作为 Section 和 Department 的组合，那么这两个组合都会被拼接成 Sec1BPO 并相加，这显然是错误的。

因此，在拼接部分时应该使用某种分隔符来排除这些重复项，例如空格或其他确定不会出现在要拼接的值中的字符。我的意思是像这样：

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

(感谢@Conrad Frix提出此问题。)

- Andriy M

---

+1 绝对是正确的做法，但您可能需要添加一个警告，即“Section + Department”可能会导致某些数据的正确性问题。添加分隔符“Section + '|' + Department”可以解决这个问题。 - Conrad Frix

@Conrad Frix：你说得对。这个想法简单地没有出现在我的脑海中，因为OP的例子似乎不会引起这样的问题。尽管如此，还是应该加上警告，现在已经加上了。感谢您的提醒。 - Andriy M

Answer 2

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

要查看它的工作原理，请转到Data.SE查询SO问题6881168

请注意，我使用了表变量而不是表，因为在Data.SE上无法使用Create table

输出

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

- Conrad Frix