Member 12605293 Ответов: 2

Как добавить сведения о человеке, имеющем несколько значений в одной строке в SQL

Привет, я хочу добавить 5-ю колонку как "оставить", я получаю следующий результат с этим запросом

Empname Deptname LeaveType TotalLeave

Эндрю CSE SickLeave 3

Джордж это CasualLeave 1

Эндрю CSE CasualLeave 2

Джордж это серп 2 


Select EmployeeDetails.Empname,DepartmentDetails.Deptname ,LeaveApplication.LeaveType,Sum(LeaveApplication.NoOfDays) As TotalLeave 
From DepartmentDetails 
Inner JOIN EmployeeDetails on EmployeeDetails.DeptID = DepartmentDetails.DeptID
INNER JOIN LeaveApplication On EmployeeDetails.EmpID = LeaveApplication.EmpID
Where  LeaveApplication.LeaveFromDate >='2017-01-01' AND LeaveApplication.LeaveFromDate <='2017-05-31' and  EmployeeDetails.Status=0 and LeaveApplication.leavetype not in ('Forgot Access Card','Permission','Work from Home','Holiday Allowance/Weekend Allowance','On Duty','Night Shift Allowance') and LeaveApplication.LeaveStatus<>'Rejected'
GROUP BY LeaveApplication.EmpID ,DepartmentDetails.Deptname,EmployeeDetails.Empname,LeaveApplication.LeaveType
это мой запрос




Результат Нужен Как

Empname Deptname LeaveType TotalLeave

Эндрю CSE SickLeave, Casual 5

Джордж ИТ Казуаллив, Серп 3

Что я уже пробовал:

Select EmployeeDetails.Empname,DepartmentDetails.Deptname ,LeaveApplication.LeaveType,Sum(LeaveApplication.NoOfDays) As TotalLeave 
From DepartmentDetails 
Inner JOIN EmployeeDetails on EmployeeDetails.DeptID = DepartmentDetails.DeptID
INNER JOIN LeaveApplication On EmployeeDetails.EmpID = LeaveApplication.EmpID
Where  LeaveApplication.LeaveFromDate >='2017-01-01' AND LeaveApplication.LeaveFromDate <='2017-05-31' and  EmployeeDetails.Status=0 and LeaveApplication.leavetype not in ('Forgot Access Card','Permission','Work from Home','Holiday Allowance/Weekend Allowance','On Duty','Night Shift Allowance') and LeaveApplication.LeaveStatus<>'Rejected'
GROUP BY LeaveApplication.EmpID ,DepartmentDetails.Deptname,EmployeeDetails.Empname,LeaveApplication.LeaveType

RickZeeland

Я хотел бы отметить, что в PostgreSQL это можно сделать просто с помощью функции string_agg() :
выберите string_agg(leavetype,',')

2 Ответов

Рейтинг:
8

BinnyVishwant 

Try this method............
DECLARE @Table1 TABLE(name varchar(20),branch varchar(20),leavetype varchar(20),noofleaves  INT)
INSERT INTO @Table1 VALUES ('A','CSE','sick',2),('B','IT','cough',1),('A','cse','allergy',4),('B','xx','vomit',2)


SELECT  name
       ,STUFF((SELECT ', ' + CAST(leavetype AS VARCHAR(10)) [text()]
         FROM @Table1 
         WHERE name = t.name and branch=t.branch
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
        ,sum(noofleaves)
FROM @Table1 t
GROUP BY name,branch

CHill60

Хороший, но только 4*... OP может изо всех сил пытаться применить это к своей структуре таблиц.

Рейтинг:
18

CHill60

Не решение этой конкретной проблемы, а "решение" - это лучший способ представить некоторые советы, которые могут помочь вам, когда вы столкнетесь с проблемами позже.

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

Select ED.Empname,DepartmentDetails.DD ,LA.LeaveType,Sum(LA.NoOfDays) As TotalLeave 
From DepartmentDetails DD
Inner JOIN EmployeeDetails ED on ED.DeptID = DD.DeptID
INNER JOIN LeaveApplication LA On ED.EmpID = LA.EmpID
Where  LA.LeaveFromDate >='2017-01-01' AND LA.LeaveFromDate <='2017-05-31' and  ED.Status=0 and LA.leavetype not in ('Forgot Access Card','Permission','Work from Home','Holiday Allowance/Weekend Allowance','On Duty','Night Shift Allowance') and LA.LeaveStatus<>'Rejected'
GROUP BY LA.EmpID ,DD.Deptname,ED.Empname,LA.LeaveType

2. Если вы используете версию SQL Server начиная с 2008 года, то BETWEEN оператор на свиданиях гораздо аккуратнее. Также рассмотрите возможность использования разрывов строк и пробелов, чтобы сделать ваш запрос более легким для чтения. Вот ваш первоначальный запрос снова, со всеми этими добавленными предложениями
Select ED.Empname,DepartmentDetails.DD ,LA.LeaveType,Sum(LA.NoOfDays) As TotalLeave 
From DepartmentDetails DD
Inner JOIN EmployeeDetails ED on ED.DeptID = DD.DeptID
INNER JOIN LeaveApplication LA On ED.EmpID = LA.EmpID
Where  LA.LeaveFromDate BETWEEN '2017-01-01' AND '2017-05-31' and  ED.Status=0 
	and LA.leavetype not in ('Forgot Access Card','Permission','Work from Home','Holiday Allowance/Weekend Allowance','On Duty','Night Shift Allowance') 
	and LA.LeaveStatus<>'Rejected'
GROUP BY LA.EmpID ,DD.Deptname,ED.Empname,LA.LeaveType
3. Подумайте о том, чтобы перевести нормализацию вашей схемы на другой уровень. Вы повторяете много текста, когда речь заходит о LeaveApplication и списке типов отпусков, которые нужно опустить. У вас должен быть еще один столик для LeaveType который содержит текст типа leave и должен ли он быть включен в запрос например 
DECLARE @LeaveType TABLE (id int identity(1,1), LeaveType nvarchar(40), Acceptable bit)
INSERT INTO @LeaveType (LeaveType, Acceptable) VALUES
('SickLeave',1),('CasualLeave',1),
('Forgot Access Card',0),('Permission',0),('Work from Home',0),
('Holiday Allowance/Weekend Allowance',0),('On Duty',0),('Night Shift Allowance',0)
LeaveType в LeaveApplication должен быть целочисленный внешний ключ к этой новой таблице например 
DECLARE @LeaveApplication TABLE(EmpID INT, LeaveType INT, LeaveFromDate date, LeaveStatus varchar(20), NoOfDays INT)
insert into @LeaveApplication (EmpID, LeaveType, LeaveFromDate, LeaveStatus, NoOfDays) values
(1, 1, '2017-02-14', 'Accepted', 3),
(1, 2, '2017-03-15','Accepted',2),
(2, 2, '2017-03-15','Accepted',1),
(2, 1, '2017-03-15','Accepted',2),
(1, 4, '2017-03-20','Accepted',1),
(2, 7, '2017-03-20','Accepted',1)

4.вы также можете использовать подзапросы и общие табличные выражения, чтобы сделать ваши запросы более понятными, более простыми в построении и отладке. Например, я мог бы поместить ваш (теперь исправленный) исходный запрос в CTE. Затем я могу относиться к этому так, как если бы это была таблица образцов из решения 1 - что облегчает применение этой техники:
;WITH QRY AS 
(
	SELECT  ED.Empname,DD.Deptname ,I.LeaveType, SUM(LA.NoOfDays) As TotalLeave 
	FROM @DepartmentDetails DD
	Inner JOIN @EmployeeDetails ED on ED.DeptID = DD.DeptID
	INNER JOIN @LeaveApplication LA On ED.EmpID = LA.EmpID
	INNER JOIN @LeaveType I ON I.id = LA.LeaveType AND I.Acceptable = 1
	Where  LA.LeaveFromDate BETWEEN '2017-01-01' AND '2017-05-31' 
		and  ED.[Status]=0 and LA.LeaveStatus<>'Rejected'
	GROUP BY LA.EmpID ,DD.Deptname,ED.Empname,I.LeaveType
)
SELECT EmpName, DeptName 
       ,STUFF((SELECT ', ' + CAST(LeaveType AS VARCHAR(20)) [text()]
         FROM QRY 
         WHERE Empname  = t.Empname
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output,sum(TotalLeave) AS TotalLeave
FROM QRY  t
GROUP BY EmpName, DeptName