Part 3 SQL Problem & Solution How to collect data from different columns into single column
Part 3 SQL Problem & Solution How to collect data from different columns into single column
SQL Hindi Playlist : • SQL Tutorials-Hindi
MS SQL English Playlist : • SQL Tutorial-English
#mssqlserver , #sqlserver , #mssql , #database , #technology , #nosql , #informationtechnology , #sqldeveloper , #dba , #mysqldeveloper , #sqlservergeeks , #sqlservers, #sqlserveres , #mysql , #databaseadministrator , #mysqlserver , #trends ,
#sql #sqlserver #education #sqlinterviewquestions #mssqlserver #sqlforbeginners #sqltutorial #sqltraining #sqlcourse
CREATE TABLE Emp_Details (
EMPID int,
Gender varchar,
EmailID varchar(30),
DeptID int)
INSERT INTO Emp_Details VALUES (1001,'M','[email protected]',104)
INSERT INTO Emp_Details VALUES (1002,'M','[email protected]',103)
INSERT INTO Emp_Details VALUES (1003,'F','[email protected]',102)
INSERT INTO Emp_Details VALUES (1004,'F','[email protected]',104)
INSERT INTO Emp_Details VALUES (1005,'M','[email protected]',101)
INSERT INTO Emp_Details VALUES (1006,'M','[email protected]',100)
INSERT INTO Emp_Details VALUES (1007,'F','[email protected]',102)
INSERT INTO Emp_Details VALUES (1008,'M','[email protected]',102)
INSERT INTO Emp_Details VALUES (1009,'F','[email protected]',100)
Solution 1 Using String_Agg Function
select deptID, string_agg(EmailID,':') withIn Group(Order By EmailID desc) from Emp_Details group by deptID
Soultion 2 Using Cursor
DECLARE @deptID int
Declare @emailID Varchar(2000)
Declare @DeptEmail varchar(2000);
DECLARE dept_cursor CURSOR FOR SELECT deptID FROM Emp_Details Group By DeptID
OPEN dept_cursor
FETCH NEXT FROM dept_cursor INTO @deptID
WHILE @@FETCH_STATUS = 0
BEGIN
SEt @DeptEmail='';
DECLARE email_cursor CURSOR FOR SELECT emailID FROM Emp_Details Where DeptID=@deptID order by EmailID
OPEN email_cursor
FETCH NEXT FROM email_cursor INTO @emailID
WHILE @@FETCH_STATUS = 0
BEGIN
SEt @DeptEmail=@DeptEmail+@emailID+';'
FETCH NEXT FROM email_cursor INTO @emailID
END
Print Concat('DeptID: ',@deptID,' Emails: ', @DeptEmail)
CLOSE email_cursor;
DEALLOCATE email_cursor;
FETCH NEXT FROM dept_cursor INTO @deptID
END
CLOSE dept_cursor;
DEALLOCATE dept_cursor;