Транспонировать строки после Join и Groupby в таблице SQL Server

#sql-server #string #group-by #pivot #window-functions

#sql-сервер #строка #группировать по #сводная #окно-функции

Вопрос:

У меня есть несколько таблиц SQL Server:

Сотрудники [EmpID (pk), имя, обозначение]
Проекты [PID (pk), EmpID (fk), PCode, PName, Dept]

Таблицы имеют отношение многие к одному, то есть один сотрудник может работать над несколькими проектами.

Я хочу показать результат таким образом, чтобы у каждого сотрудника была только одна строка, а все проекты, над которыми он работал, были показаны в разделе столбца, сгруппированном в соответствии с PCode.

Я не могу понять, как транспонировать строки в столбец, когда количество сгенерированных столбцов неизвестно.

Пример данных:

Сотрудник:

 EmpId  Name  Designation
------------------------
001    Mat   Manager
002    Ash   Developer
003    Paul  Analyst

Проекты:

 PID EmpID PCode PName       Dept
-------------------------------------
1   001   111   Project1    Sales
2   001   111   Project1.1  Retail
3   001   222   Project2    Banking
4   002   222   Project2.1  Retail

Пример вывода:

 EmpID Name Project-1           Project-2              Project-3           
---------------------------------------------------------------------------
001   Abc  111,Project1,Sales  111,Project1.1,Retail  222,Project2,Banking
002   Def  222,Project2.1,Retail

Ответ №1:

Вы можете перечислить проекты каждого сотрудника в подзапросе с помощью row_number() , затем использовать условную агрегацию для поворота во внешнем запросе:

 select e.empid, e.name,
    max(case when rn = 1 then concat_ws(',', p.pcode, p.pname, p.pdept) end) project1,
    max(case when rn = 2 then concat_ws(',', p.pcode, p.pname, p.pdept) end) project2,
    max(case when rn = 3 then concat_ws(',', p.pcode, p.pname, p.pdept) end) project3
from employee e
inner join (
    select p.*, row_number() over(partition by empid order by pid) rn
    from projects p
) p on p.empid = e.empid
group by e.empid, e.name

1. Это выдает мне эту ошибку «Код ошибки: 1064 У вас ошибка в вашем синтаксисе SQL; проверьте руководство, соответствующее вашей версии сервера MySQL, на предмет правильного синтаксиса для использования near «(раздел по порядку empid по pid) rn

2. @user8306074: это сообщение об ошибке MySQL. Почему вы отметили свой вопрос SQL Server? row_number() Я доступен только в MySQL 8.0.

3. У меня огромный набор данных, и я не знаю максимального количества проектов для каждого сотрудника. Итак, в этом сценарии, как я могу сгенерировать несколько столбцов проекта?

Ответ №2:

Вы можете использовать PIVOT команду для транспонирования ваших данных. В вашем случае, который вам неизвестен, количество сгенерированных столбцов неизвестно. Вам нужно запросить, чтобы найти имя столбца сгенерированного столбца, затем вы можете объединить все имена вашего столбца с помощью PIVOT команды, как показано ниже в инструкции запроса.

 DECLARE @columnName NVARCHAR(MAX), @pivotSql NVARCHAR(MAX)
SELECT 
    em.EmpID,
    em.Name,
    pr.Pname,
    pr.Pcode   ','   pr.PName   ','   pr.Dept Detail
INTO #temp
FROM Employee em 
INNER JOIN Projects pr ON em.EmpID = pr.EmpID 

SELECT 
    t.PName name
INTO #AllProject
FROM #temp t
GROUP BY t.PName

SET @columnName = '';

DECLARE 
    @projectName VARCHAR(MAX);

DECLARE cursor_allProject CURSOR
FOR SELECT 
        name
    FROM 
        #AllProject;
OPEN cursor_allProject;
FETCH NEXT FROM cursor_allProject INTO @projectName;
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @columnName = @columnName   ', ['   @projectName   ']';
        FETCH NEXT FROM cursor_allProject INTO @projectName;
    END;
CLOSE cursor_allProject;
DEALLOCATE cursor_allProject;


SET @pivotSql = 'SELECT t.EmpID, t.Name FROM #temp t PIVOT t.Detail (FOR t.Pname IN ('  @columnName  ') )'

EXEC (@pivotSql)