sql server 转置问题

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

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

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

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

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)

【以下回答由 GPT 生成】

这个问题可以通过使用PIVOT操作来实现。PIVOT操作可以将行数据转换为列数据，这就可以满足将部门列转置为多个部门和人数列的需求。下面是使用SQL语句实现转置操作的步骤：

步骤 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);

步骤 2: 使用PIVOT进行转置操作 然后，我们可以使用PIVOT操作将部门列转置为多个部门和人数列。首先，我们需要编写一个包含动态列的查询语句。

DECLARE @columns NVARCHAR(MAX), @query NVARCHAR(MAX);

SELECT @columns = STUFF(
    (
        SELECT DISTINCT ',' + QUOTENAME(DepartmentName) + ', ' 
        + 'Number AS ' + QUOTENAME('部门' + CAST(ROW_NUMBER() OVER(PARTITION BY Company ORDER BY DepartmentName) AS VARCHAR)) 
        FROM Department
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
);

SET @query = 'SELECT Company, ' + @columns + 'FROM 
               (
                   SELECT Company, DepartmentName, Number,
                   ''部门'' + CAST(ROW_NUMBER() OVER(PARTITION BY Company ORDER BY DepartmentName) AS VARCHAR) AS DepartmentNumber
                   FROM Department
               ) x
               PIVOT
               (
                   MAX(DepartmentName) FOR DepartmentNumber IN (' + @columns + ')
               ) p';

EXECUTE(@query);

执行上述查询语句后，就可以得到相应的转置结果。

以上就是使用SQL语句实现SQL Server转置操作的具体步骤。如果你有任何问题，请随时向我提问。

【相关推荐】

• 这篇博客: SQL语句分类以及详解中的 左外连接：用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL，可以理解为：在内连接的基础上保证左表的数据全部显示 部分也许能够解决你的问题。