sql server 转置问题

Question

请教各位。
数据库中我有一个数据表

公司	部门	人数
A	行政部	20
A	技术部	15
A	维护部	10
B	技术部	13
B	业务部	23

希望通过SQL语句，得到以下的结果：

公司	部门1	部门1人数	部门2	部门2人数	部门3	部门3人数
A	行政部	20	技术部	15	维护部	10
B	技术部	23	业务部	13	NULL	NULL

其中列数的多少由原数据表的最大部门数决定。

请教这样应该用怎么样的SQL语句实现？

Answer 1

CREATE TABLE Department (
    Company VARCHAR(50),
    DepartmentName VARCHAR(50),
    Number INT
);

INSERT INTO Department (Company, DepartmentName, Number)
VALUES ('A', '行政部', 20),
       ('A', '技术部', 15),
       ('A', '维护部', 10),
       ('B', '技术部', 13),
       ('B', '业务部', 23);


declare @counts int
declare @num int
declare @cols varchar(max)
declare @query varchar(max)
set @counts=(  SELECT MAX(num_departments) AS max_depts  
  FROM (  
    SELECT company, COUNT(DISTINCT DepartmentName) AS num_departments  
    FROM [Department]  
    GROUP BY company  
  ) dc );
  set @num=1;
  set @cols='';
  while @num<=@counts
  begin
  set @cols+='MAX(CASE WHEN r = '+cast(@num as varchar)+' THEN DepartmentName END) AS department'+cast(@num as varchar)+',
  MAX(CASE WHEN r = '+cast(@num as varchar)+' THEN num_employees END) AS Number'+cast(@num as varchar)+', '
  set @num+=1;
  end
  select @cols;
  set @cols=(select left(@cols,len(@cols)-1))

set @query='SELECT company,'+@cols+'     
FROM (  
  SELECT company, DepartmentName, num_employees,  
         ROW_NUMBER() OVER (PARTITION BY company ORDER BY DepartmentName) AS r  
  FROM (
  SELECT company, DepartmentName,max(Number) AS num_employees  
  FROM [Department]   
  GROUP BY company, DepartmentName  
  ) e 
) d  

GROUP BY company  
ORDER BY company; '

 exec(@query)